Skip to main content
CodeSparring

Anti-Joins: Finding Missing Matches

Level 2: Level 2: Aggregation & Joins (Combining Source Data)medium25 minanti-join (LEFT JOIN … IS NULL)semi-join conceptorphan detection

Find records that have no counterpart: the DE's referential-integrity check.

Referential integrity: find the orphans

Before you trust a source, you check its referential integrity: does every order point at a real customer? Does every order_item point at a real product? Rows that point at a nonexistent parent are orphans, and finding them is a DE's daily hygiene. The pattern is the anti-join: "give me every left row that has no match on the right."

The portable recipe is a LEFT JOIN plus an IS NULL filter:

SELECT o.order_id, o.customer_id
FROM orders AS o
LEFT JOIN customers AS c
  ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;   -- keep ONLY the rows that failed to match

The LEFT JOIN keeps every order and NULL-pads the customer columns for unmatched orders. The WHERE c.customer_id IS NULL then keeps only those NULL-padded rows: the orphans. Every matched order is discarded because its c.customer_id is non-NULL.

orders · ANTI JOIN · customers ON customer_id = customer_id
Step 1 / 4
orders
order_idcustomer_id
1001
1019
1022
1037
customers
customer_id
matched: 1
2
3

customer_id=1 matches → dropped (ANTI JOIN keeps only non-matches).

Result (0 rows)
orders.order_idorders.customer_idcustomers.customer_id
no rows yet
Anti-join keeps only orders with NO matching customer: orders 101 (customer 9) and 103 (customer 7) are the orphans; matched orders 100 and 102 drop out.

Anatomy:

LEFT JOIN customers c ON ...     → matched orders get c.*, orphans get NULLs
WHERE c.customer_id IS NULL      → survives ONLY if there was NO match  ← the anti-join

Two siblings, one distinction:

  • Anti-join = rows with no match (what we just wrote).
  • Semi-join = rows with a match, but you don't want the right table's columns, classically written WHERE EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id) or WHERE o.customer_id IN (SELECT customer_id FROM customers). Use it when you only need to confirm a match exists, not pull data from it.

In the warehouse this differs. NOT IN is a tempting shorthand for an anti-join, but it has a NULL landmine: if the subquery's list contains even one NULL, NOT IN returns no rows at all (three-valued logic: x NOT IN (…, NULL) is never true). The LEFT JOIN … IS NULL and NOT EXISTS patterns are NULL-safe and work identically across SQLite, Postgres, and every warehouse. Prefer them.

Keep it readable / common pitfall: the IS NULL must reference a column that is guaranteed non-NULL in matched rows: the join key or the right table's primary key. If you IS NULL-check a nullable right column, you'll misclassify matched rows (that legitimately have a NULL there) as orphans.

Recap: An anti-join finds rows with no counterpart via LEFT JOIN … WHERE right.key IS NULL, the backbone of orphan/FK checks; prefer it (or NOT EXISTS) over NOT IN, which breaks on NULLs.

Sample data for this example
CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY
);
CREATE TABLE orders (
  order_id    INTEGER PRIMARY KEY,
  customer_id INTEGER
);
INSERT INTO customers VALUES (1),(2),(3);
INSERT INTO orders VALUES
  (100, 1),
  (101, 9),    -- orphan: customer 9 doesn't exist
  (102, 2),
  (103, 7);    -- orphan: customer 7 doesn't exist
Worked example (SQL)
SELECT o.order_id, o.customer_id
FROM orders AS o
LEFT JOIN customers AS c
  ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

Apply

Your turn

The task this lesson builds to.

Find orders whose customer_id has no matching row in customers (orphaned orders). Return order_id and customer_id, sorted by order_id.

3 hints and 1 automated check are waiting in the workspace.

Practice

Make it stick

A second problem on the same idea, so it survives past today.

Write a query that returns the order_item_id and product_id of every orphaned line item: an order_items row whose product_id has no matching row in products. Sort by order_item_id.

3 hints and 1 automated check are waiting in the workspace.