Level 7 · Nobody has ever skipped the website?

An empty result is a result too. The starter says no customer ever skipped the website, and that is exactly what a NOT IN bug looks like.

Why one NULL empties the result

x NOT IN (a, b, c) means x <> a AND x <> b AND x <> c. If one of the values is NULL, x <> NULL is not true or false but unknown, and an AND chain that contains an unknown can never be true. The row is filtered out.

web_sessions has 23,002 anonymous sessions with customer_id NULL. So for every customer, customer_id NOT IN (SELECT customer_id FROM web_sessions) is unknown, and the query returns nothing. Note that IN does not have this problem: a match is a match, whatever else is in the list. That is why the with_orders column in the starter is fine.

How to spot it

• An anti-join (NOT IN, “never”, “without”) that returns zero rows is suspicious on its own.
• Check the subquery column for NULLs: SELECT COUNT(*) FROM web_sessions WHERE customer_id IS NULL returns 23,002.
• Count the other side: 1,813 distinct customers have a session, out of 2,018 customers. The answer cannot be zero.

The fix

NOT EXISTS looks for a matching row, and a NULL never matches:

WHERE NOT EXISTS (
  SELECT 1 FROM web_sessions AS w
  WHERE w.customer_id = c.customer_id
)

The result: 205 customers, 29 business and 176 consumer. Of those, 20 business and 101 consumer customers have placed an order, so they buy without ever showing up in web tracking (on the app or a marketplace, for example).

Filtering the NULLs out also works: NOT IN (SELECT customer_id FROM web_sessions WHERE customer_id IS NOT NULL) returns the same 205. A LEFT JOIN ... WHERE w.session_id IS NULL works as well, but NOT EXISTS states the intent most clearly.

Common mistakes

• Adding COALESCE(customer_id, 0) inside the subquery: it works only as long as no real id is 0.
• Testing the NOT IN on a small sample without anonymous sessions, where it seems to work.
• Reporting “zero” without checking it. An empty anti-join deserves the same suspicion as a total that doubled.

In SQL Server

The behavior is identical under the default ANSI_NULLS ON: one NULL in the list and NOT IN returns nothing. The same NOT EXISTS works as written, and ISNULL(x, 0) is T-SQL’s two-argument cousin of COALESCE, with the same caveat as above.

Interview note

“Why does this NOT IN query return no rows?” is a classic. The answer is three-valued logic with NULL, and the fix is NOT EXISTS.