NULLs That Mean "No Match"

Concepts covered: Nulls in Joins, Null Handling

A LEFT JOIN result can hold 2 kinds of NULL, and they look identical in the output. The first kind is produced: the engine found no partner for a left row and filled every right-side column with NULL at once, primary key included. The second kind is stored: a column in the right table holds NULL as its actual value, on a row that did match. The anti-join filter depends on telling them apart, and IS NULL on its own cannot, so the choice of which column to test is what does the telling.

One result, 2 kinds of empty

Order 102 has shipped_date NULL because Bob's order hasn't left the warehouse yet. That NULL was written by the application and sits in the orders table. Carol's row has NULL in o.shipped_date too, and also in o.order_id and o.customer_id, and none of those values exist anywhere in orders; the join invented them because there was no order row to copy from. Bob's row is a match with a blank in it. Carol's row is a non-match.

The wrong column lets a stored NULL through

SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
WHERE o.shipped_date IS NULL;

The animation runs on a 3-customer slice of the running example: Alice with shipped order 101, Bob with order 102 still waiting on its shipped_date, and Carol with no order at all. The query tests o.shipped_date IS NULL and returns 2 rows: Bob and Carol. Bob's row passes because the stored value is NULL. Carol's passes because the produced value is NULL. The filter asked a column a yes-or-no question and both rows said yes. Bob has placed an order, so he does not belong in "customers who never ordered", and the query has mixed "never ordered" with "ordered and not yet shipped" without raising an error, leaving the row count off by 1.

Test a column that is never NULL on a match

On a matched row, 2 right-side columns cannot be NULL. The first is the right table's primary key, o.order_id, which the table never stores as NULL. The second is the join column itself, o.customer_id: on any matched row it equals c.customer_id, and an equality can only be TRUE between 2 real values, so a matched row can't carry NULL there. Either column is NULL on an unmatched row and only on an unmatched row, which is exactly the test the anti-join needs. The habit to show the interviewer is reaching for o.order_id IS NULL or o.customer_id IS NULL and never for a business column like shipped_date.

-- Customers who have never ordered, proven by the primary key
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

On the full running example in the table above, this returns 1 row, Carol. Bob's row carries 102 in o.order_id, fails the filter and is dropped along with Alice's 2 rows and Dana's, whatever shipped_date holds. The business column is still in the join output, so if the question later becomes "customers whose orders have all shipped" you can filter on it deliberately, knowing that you're now testing stored values and that the question has changed.

A stored NULL in the join column itself

Suppose a bad load inserts order 105 with customer_idNULL. In the ON condition, NULL = 5 evaluates to UNKNOWN, and a join emits a pair only when the condition is TRUE, so order 105 pairs with nobody and never appears in the LEFT JOIN output at all. Carol's row is still produced with its manufactured NULLs, the filter still returns exactly her, and the orphan order has changed nothing. This form of the anti-join is indifferent to NULLs stored in the excluding table's join column, which is the sentence to say when the interviewer asks "what if orders has a NULL customer_id?".

Produced NULL

• Appears in every right-side column at once, primary key included
• Exists nowhere in the stored table
• Means: no row satisfied the ON condition
• The value the anti-join filter is looking for

Stored NULL

• Appears in 1 column, with real values beside it
• Was written by an application or a load job
• Means: this fact is unknown or not yet set
• Invisible to the filter when you test the primary key

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.