EXISTS, IN and the NULL trap in SQL

The two read alike until the subquery returns a NULL, and then they disagree.

SELECT name
FROM customers AS c
WHERE EXISTS (
        SELECT 1
        FROM orders AS o
        WHERE o.customer_id = c.id
            AND o.status = 'paid'
    );

SELECT name
FROM customers AS c
WHERE NOT EXISTS (
        SELECT 1
        FROM payments AS p
        WHERE p.order_id = c.id
    );

How it works

EXISTS stops at the first matching row; it never compares values.
NOT IN over a subquery containing a NULL returns no rows at all.
NOT EXISTS is the form that keeps behaving when the data gets messy.

Keywords and builtins used here

AND
AS
EXISTS
FROM
NOT
SELECT
WHERE
c

The run, in numbers

Lines: 16
Characters to type: 231
Tokens: 57
Three-star pace: 95 tpm

At the three-star pace of 95 tokens a minute, this run takes about 36 seconds.

Type this snippet

Step 2 of 3 in Writing it faster, step 9 of 17 in Plans & performance.

← Previous Next →