NOT IN vs NULL gotchas

Concepts covered: Three-Valued Logic

Back to the Black Friday email. The natural first draft asks for stores whose id isn't IN the list of order store ids, and it returns 0 rows even though Fern Studio and Pixel Prints have never sold anything. The cause is order 105 and its NULL store_id.

What NOT IN expands to

x NOT IN (a, b, c) is shorthand for x <> a AND x <> b AND x <> c. The subquery here returns 1, 1, 3 and 5, plus a NULL from order 105, so for store 2 the filter becomes 2 <> 1 AND 2 <> 1 AND 2 <> 3 AND 2 <> 5 AND 2 <> NULL. Any comparison with NULL yields NULL, which means unknown. An AND that holds a NULL and no FALSE is NULL as a whole. WHERE keeps a row only when its condition is TRUE, so store 2 is dropped. The table runs the same check for all 5 stores.

Stores that have orders come out FALSE, which is the intended answer. Stores without orders come out NULL, which WHERE treats the same as FALSE. Every row loses, and the query raises no error.

NOT IN against an allowlist with a NULL

The animation shows a security version. An allowlist meant to exempt user 2 picked up a NULL from a bad load. User 1 appears nowhere in the allowlist, and neither do users 3 and 4, yet none of them comes back, because each one's comparison against the NULL is unknown.

SELECT user_id
FROM logins
WHERE user_id NOT IN (
  SELECT user_id
  FROM risk_allowlist
)

NOT EXISTS ignores the NULL order

For store 2, the NOT EXISTS subquery tests each order in turn: 1 = 2 is FALSE, 1 = 2 is FALSE, 3 = 2 is FALSE, 5 = 2 is FALSE and NULL = 2 is NULL. The inner WHERE also keeps only TRUE rows, so the subquery returns nothing and NOT EXISTS is TRUE. An unknown comparison can never make a subquery nonempty, so the NULL order has no effect. IN is safe for the opposite reason: it expands to comparisons joined by OR, and once a value matches, the OR is TRUE whatever else the list holds.

NOT IN

Returns 0 rows: the NULL store_id makes every non-matching comparison unknown.

SELECT
  store_id,
  store_name
FROM stores
WHERE store_id NOT IN (
  SELECT store_id
  FROM orders
)

NOT EXISTS

Returns Fern Studio and Pixel Prints, the 2 stores with no orders.

SELECT
  store_id,
  store_name
FROM stores
WHERE NOT EXISTS (
  SELECT *
  FROM orders
  WHERE orders.store_id = stores.store_id
)

IN

Returns Maple Mugs, North Knits and Tidal Tees. A NULL in the list cannot block a real match.

SELECT
  store_id,
  store_name
FROM stores
WHERE store_id IN (
  SELECT store_id
  FROM orders
)

Do

• Write exclusions as NOT EXISTS with the link inside the subquery
• Use NOT IN only with a literal list you typed and can read
• Treat a 0-row exclusion result as a bug until you prove otherwise
• Expect NOT EXISTS to keep outer rows whose own key is NULL

Avoid

• Run NOT IN against a subquery on a column that allows NULL
• Put NULL in a literal NOT IN list to mean no value
• Trust a NOT IN that passed testing: tomorrow's load may add a NULL
• Read a missing error as proof that the filter worked

Which stores does this query return?

SELECT
  store_name
FROM stores
WHERE store_id NOT IN (2, 4, NULL)

• Maple Mugs, North Knits and Tidal Tees
• Fern Studio and Pixel Prints only
• An error, since NULL cannot sit in a number list
• No stores, since every condition is FALSE or NULL

About this lesson

This section is part of the Filtering: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.