Anti-Join vs Plain Filter

Concepts covered: Filtering, Outer Joins, Execution Order

A WHERE clause evaluates one row at a time, with the columns that row carries and nothing else. The customers row for Carol holds a customer_id and a name; it has no field that says whether an order exists for her, and the orders that would have said so live in another table and, in her case, do not exist. "Has never ordered" is a fact about the entire orders table, and no filter on customers alone can see it. Candidates who have not internalized this write 3 queries that each look right for a moment.

Filter 1: IS NULL on the wrong table

-- Looks for missing customers in the wrong place
SELECT customer_id, name
FROM customers
WHERE customer_id IS NULL;

This asks which customers have no id, and returns 0 rows because every customer has one. Pointing the same test at orders (SELECT * FROM orders WHERE customer_id IS NULL) finds orders with a missing customer, which are stored NULLs and a different question. No single table has a column whose emptiness means "no order exists for this customer", so no single-table IS NULL can find Carol.

Filter 2: the inequality join

-- "Customers whose id differs from an order's customer" is a different question
SELECT DISTINCT c.customer_id, c.name
FROM customers c
JOIN orders o
  ON o.customer_id <> c.customer_id;

This reads as "customers with no matching order" and returns all 4 customers. The join pairs every customer with every order whose customer_id differs: Carol pairs with all 4 orders, Alice with 102 and 104, Bob with 101, 103 and 104, Dana with 101, 102 and 103. That is 12 pairs out of 16 possible, and every customer sits in at least one, so DISTINCT hands back the full customer list. The query answered "is there some order that is not this customer's", which is true for anyone as soon as 2 different customers have ordered. "No order matches" and "some order does not match" are different claims, and <> can only make the second.

Filter 3: INNER JOIN then IS NULL

-- The filter is right; the join has already removed the row it needed
SELECT c.customer_id, c.name
FROM customers c
JOIN orders o
  ON o.customer_id = c.customer_id
WHERE o.customer_id IS NULL;

The WHERE here is the anti-join's own filter, and the query returns 0 rows because, as section 1 showed, the inner join never emits Carol. This is the query a candidate writes after memorizing the IS NULL ending without the step that feeds it. The NULL this filter tests exists in no stored table. The LEFT JOIN produces it, and that is the moment the absence of an order becomes a value on Carol's row. Replace LEFT with INNER and the filter waits for a value nothing creates. The follow-up an interviewer asks on seeing this is "where did you expect that NULL to come from?", and the answer that recovers the round is "from the outer join, which I need to put back".

The LEFT JOIN puts the absence on the row

The LEFT JOIN converts a property of a set into a property of a row. After it runs, each customer row carries either a partner's id or a NULL, and "no order exists for this customer" has become a value in a column on this customer's row. Only then does WHERE have something to test. Every anti-join written as a join is this 2-step move: bring the absence onto the row, then filter on it. A candidate who can say that sentence can also explain why the LEFT can't be dropped and why the filter column has to come from the right side.

A has_ordered flag on customers would make the plain filter work, and interviewers sometimes offer one to see what you do. It's a stored answer to the question, and it's correct only while some process updates it on every order and every deletion. The follow-up is "how does that flag stay right?", and the answer is a job that runs the same anti-join on a schedule. Say that, and you have shown you know where derived flags come from and what they cost to keep true.

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.