Drawing the Two Sets First

Concepts covered: Outer Joins

The anti-join is short enough that writing it first feels fast, and that is where the wrong answers come from. Underneath the 6 lines sit 4 decisions: which table's rows you keep, which table supplies the evidence that excludes a row, which columns link the 2, and which right-side column proves a row unmatched. Drawing those 4 before typing takes 20 seconds and settles every clause of the query, so the typing becomes transcription.

The sketch for the running example

Swap the sets and you have a different question

Put orders on the left and the query answers "orders whose customer is missing from the customers table", an orphan check that a finance team runs after a migration. The link is the same and the proof column moves to the other side: c.customer_id IS NULL. On this data it returns 0 rows, because every order points at a real customer, and 0 is the correct answer rather than a bug. Candidates who skip the drawing pick the kept set by whichever table they typed first, and discover the mistake when the result has the wrong columns in it.

-- Orders with no customer: same tables, the other set is kept
SELECT o.order_id, o.customer_id
FROM orders o
LEFT JOIN customers c
  ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

A date condition on the excluding set

The follow-up most interviewers keep ready is a date. Suppose orders also carries order_date, and the question becomes "customers with no order since 2024-01-01". The kept set is unchanged. The excluding set is now the orders placed since January, and the condition that shrinks it belongs in the ON clause, where it decides what counts as a partner. The tempting version keeps the join exactly as it was and adds the date to the outer WHERE, next to the proof column.

-- Wrong: the date filters the joined rows, not the excluding set
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
WHERE o.order_date >= DATE '2024-01-01'
  AND o.order_id IS NULL;

Every joined row fails 1 of the 2 conditions. Matched rows carry a real o.order_id, so they fail IS NULL whatever their date says. Carol's manufactured row passes IS NULL, but its o.order_date is NULL, so the date comparison is UNKNOWN, and WHERE keeps only rows where every condition is TRUE. No row can pass both halves, so the query returns 0 rows on any data: a filter no row can pass, written to look like an anti-join. Moving the date into the ON clause changes what counts as a partner instead of what counts as a result. An order from before January no longer pairs with its customer, so a customer whose orders are all older comes out of the join with manufactured NULLs, and the IS NULL proof keeps them.

-- The condition on the excluding set lives in the ON clause
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
 AND o.order_date >= DATE '2024-01-01'
WHERE o.order_id IS NULL;

Drawn as sets, the rule is immediate: anything that defines which right-side rows count is part of the excluding set and goes in the ON; the only thing in the WHERE is the IS NULL proof, plus any condition on the kept set itself, such as c.status = 'active', which is safe there because kept-set columns are never manufactured. Saying "the date condition is part of what an order means here, so it goes on the join" is the sentence interviewers listen for, because it shows the filter was placed by reasoning.

If the interviewer asks for a second way to write it, NOT EXISTS with a correlated subquery is the other standard form; its mechanics, and the trap inside NOT IN, belong to the intermediate lesson.

Do

• Say the kept set, the excluding set and the join column aloud before writing the join
• Test IS NULL on the right table's primary key or its join column
• Put conditions on the excluding set in the ON clause
• Trace 1 matched and 1 unmatched row through the join before running
• State the row-count bound from the kept table's grain

Avoid

• Test IS NULL on a business column that the table can store as NULL
• Write INNER JOIN and expect the WHERE to find rows the join dropped
• Use <> in the ON clause to mean "no match"
• Put a date or status filter on the right table in the outer WHERE
• Pick the kept table by whichever name you typed first

About this lesson

This section is part of the Anti-Join: Beginner 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.