SQL Querying

EXISTS, IN, and NULL Traps

EXISTS asks whether a subquery returns any row, while IN compares a value with a set of candidate values. Null introduces UNKNOWN, making NOT IN unsafe when its candidate set can contain null; NOT EXISTS is the robust anti-join form.

EXISTS depends only on whether at least one row exists, not on the values projected by its subquery.
IN is concise membership syntax and duplicates in its candidate set do not change the truth result.
NOT IN is not simply “no equal value.”
NOT EXISTS is null-safe when the correlation uses the intended equality.
A left anti join can also use LEFT JOIN ... WHERE right_key IS NULL, but the tested right column must be non-null for matched rows.
Empty candidate sets matter: NOT EXISTS over an empty subquery is true for every outer row, matching the anti-join meaning.

Why one null can invalidate NOT IN

Membership and anti-membership behavior for outer value 42
Candidate rows	`42 IN (...)`	`42 NOT IN (...)`	Reason
`(7, 42)`	`TRUE`	`FALSE`	An equal candidate exists
`(7, 9)`	`FALSE`	`TRUE`	All comparisons are known and unequal
`(7, NULL)`	`UNKNOWN`	`UNKNOWN`	`42 <> NULL` is unknown
empty set	`FALSE`	`TRUE`	No candidate satisfies membership

Safe semi and anti joins

-- Customers with a paid order
SELECT c.customer_id, c.name
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
    AND o.status = 'PAID'
);

-- Customers with no paid order
SELECT c.customer_id, c.name
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
    AND o.status = 'PAID'
);

Equivalent left anti join when the key is non-null

SELECT c.customer_id, c.name
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
 AND o.status = 'PAID'
WHERE o.order_id IS NULL;

If a nullable orders.referrer_customer_id subquery returns (7, NULL), customer 42 fails NOT IN even though 42 is not present: 42 <> 7 is true but 42 <> NULL is unknown. The NOT EXISTS version asks only whether a row with referrer_customer_id = 42 exists and correctly keeps customer 42.