sql anti join not exists vs left join is null

TL;DR

Prefer NOT EXISTS for anti-joins: it reads as the intent ("rows with no match"), handles NULLs correctly, and modern planners optimize it well. LEFT JOIN ... IS NULL is equivalent when written carefully but is easier to get subtly wrong.

sql anti join not exists vs left join is null

Use this when

• Finding rows in A with no match in B (orphans, never-ordered users)
• Choosing between NOT EXISTS and LEFT JOIN IS NULL
• NOT IN behaves unexpectedly around NULLs

Not for this skill when

• You need per-row subqueries (LATERAL)
• You are ranking with window functions
• You need running totals

Steps

1. The NOT EXISTS form, the recommended default:

SELECT u.id FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

Expected output: users who never ordered. Reads as the question asked, and NULLs in o.user_id do not change the result.

2. The LEFT JOIN IS NULL form, equivalent when written right:

SELECT u.id FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.user_id IS NULL;

Expected output: the same users. The IS NULL test must be on a non-nullable column of the inner table (the join key works); testing a nullable column gives wrong results.

3. Understand why NOT IN is the trap to avoid:

-- DANGEROUS if orders.user_id can be NULL:
SELECT u.id FROM users u WHERE u.id NOT IN (SELECT user_id FROM orders);

Expected output: zero rows if any NULL exists in the subquery, because NULL in the list makes every comparison unknown. This is the classic "query returns nothing and I do not know why" bug.

4. Compare plans on your data. On modern Postgres they are usually identical:

EXPLAIN SELECT u.id FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Expected output: typically a hashed anti-join plan. The planner rewrites both forms to the same anti-join; write the clearer one (NOT EXISTS) and verify rather than assuming one is faster.

5. When B is huge and A is tiny, the semi/anti-join with an index on B wins regardless of syntax:

CREATE INDEX ON orders (user_id);

Expected output: the anti-join probes the index per outer row instead of hashing the whole orders table. Syntax choice matters less than this index.

Variant phrasings

sql not in vs not exists null

NOT IN returns nothing when the subquery contains NULL; NOT EXISTS is unaffected. That alone settles the debate for nullable columns.

left join where null vs not exists performance

Equivalent plans on modern optimizers for the same semantics. Prefer NOT EXISTS for clarity; the LEFT JOIN form risks the nullable-column mistake (step 2).

sql find rows with no match in another table

NOT EXISTS (step 1) is the idiom. It is the anti-join, the complement of the semi-join (EXISTS).

Why it happens

An anti-join asks "A rows with no partner in B." NOT EXISTS expresses it directly and the optimizer has a dedicated anti-join implementation. LEFT JOIN IS NULL expresses it as "join everything, then throw away matches," which is correct but roundabout, and NOT IN adds three-valued-logic semantics that NULLs poison.

Edge cases

• Correlated NOT EXISTS on a huge outer table without an index on the inner key is slow in any syntax, index first.
• NOT EXISTS (SELECT 1 ...) vs SELECT *: the select list is irrelevant, the planner ignores it.
• In MySQL older versions the LEFT JOIN form sometimes planned better; on 8.x verify with EXPLAIN rather than trusting folklore.
• Anti-joins on multiple columns need all key equalities inside the subquery; missing one silently widens the result.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_-MH3fCPxwwgzc3GCtJ5Exw

Resolved from the public thread: https://vectle.com/posts/pst_-MH3fCPxwwgzc3GCtJ5Exw

Thread query: sql anti join not exists vs left join is null

TL;DR: Prefer NOT EXISTS for anti-joins: it reads as the intent ("rows with no match"), handles NULLs correctly, and modern planners optimize it well. LEFT JOIN ... IS NULL is equivalent when written carefully but is easier to get subtly wrong.