SQL Interview Prep · Lesson

Finding Rows With No Match (Anti-Join)

The LEFT JOIN / IS NULL pattern for finding orphans and missing data.

Finding Rows With No Match (Anti-Join) is a free SQL Interview Prep lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Anti-Join Question

One of the most-asked outer-join questions: "Find customers who have never placed an order." Or: "List products that were never sold," or "orders with no matching customer."

These all share one shape: rows in one table with no match in another. The clean idiom is the anti-join, built from a LEFT JOIN plus an IS NULL filter.

The Core Idea

Start from a LEFT JOIN: it keeps every left row, and unmatched left rows get NULL in the right-table columns.

So the unmatched rows are exactly the ones where a right-table column is NULL. Filter for that, and you isolate the no-match rows. That is the entire trick.

Building the Pattern

Here is the canonical anti-join finding customers with no orders. Read it in two beats: LEFT JOIN keeps all customers, then WHERE o.customer_id IS NULL keeps only the unmatched ones.

SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
WHERE o.customer_id IS NULL;
-- only customers with zero orders

Why This Works Step by Step

Trace it with our data where Carol has no orders:

LEFT JOIN produces Alice (x2), Bob (x1), and Carol with right columns NULL.
WHERE o.customer_id IS NULL discards Alice and Bob (their right columns have real values).
Only Carol's row, the synthesized-NULL one, survives.

The filter runs after the join, so it sees those NULLs and selects precisely the orphans.

Choose the Right Column to Test

Test a right-table column that can never legitimately be NULL in a real match, ideally the join key or primary key.

If you test a nullable right column like o.shipped_at, you would also catch orders that exist but are unshipped, a wrong answer. Testing o.customer_id (the join key) or o.id (its primary key) guarantees the NULL means "no row matched."

-- SAFE: join key / primary key
WHERE o.id IS NULL

-- RISKY: a nullable data column
WHERE o.shipped_at IS NULL  -- catches unshipped too!

Anti-Join vs NOT IN

Interviewers compare the anti-join to NOT IN. They look equivalent but differ on NULLs.

If the subquery returns any NULL, NOT IN returns no rows at all, a notorious silent bug. The LEFT JOIN / IS NULL anti-join is immune to it.

-- DANGEROUS if any customer_id is NULL
SELECT id, name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- SAFE anti-join, same intent
SELECT c.id, c.name FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Anti-Join vs NOT EXISTS

The other equivalent is NOT EXISTS with a correlated subquery. It also handles NULLs correctly and is often equally fast.

All three (LEFT JOIN/IS NULL, NOT EXISTS, NOT IN) can express anti-joins, but in an interview prefer LEFT JOIN/IS NULL or NOT EXISTS for their NULL-safety. Mentioning the NOT IN trap earns points.

SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
);

A Common Mistake

A frequent error: putting the no-match condition in the ON clause instead of WHERE.

Writing ... ON o.customer_id = c.id AND o.id IS NULL does not filter the result; it just changes what counts as a match, and every customer still survives the LEFT JOIN. The IS NULL test must live in WHERE, applied after the join. We cover this trap fully in the next lesson.

Finding Orphaned Child Rows

The pattern works in the other direction too. To find orders that reference a missing customer (orphans, a data-integrity check), preserve orders and test the customer side for NULL.

SELECT o.id AS order_id, o.customer_id
FROM orders o
LEFT JOIN customers c
  ON c.id = o.customer_id
WHERE c.id IS NULL;
-- orders pointing to a non-existent customer

Counting the Orphans

Often the ask is just a count: "How many customers never ordered?" Wrap the anti-join, or count directly.

Because the anti-join already returns one row per orphan, a plain COUNT(*) on it is correct here, there is exactly one row per unmatched customer.

SELECT COUNT(*) AS never_ordered
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

The Reusable Template

Memorize this three-line skeleton; it solves a huge family of interview questions:

FROM keep_table k
LEFT JOIN other o ON o.fk = k.id
WHERE o.id IS NULL

Swap the tables and keys to find unsold products, unassigned tickets, users with no logins, anything described as "X with no matching Y."

Quick Check

You need products that have never appeared in order_items.

Recap

The anti-join finds rows with no match: LEFT JOIN then WHERE right_key IS NULL.

Test the join key or primary key, never a nullable data column.
The IS NULL test belongs in WHERE, not ON.
Equivalent to NOT EXISTS; prefer it over NOT IN, which breaks on NULLs.
Reverse the tables to find orphaned child rows.

One template, many questions: "X with no matching Y."

Frequently asked questions

Is the “Finding Rows With No Match (Anti-Join)” lesson free?

Yes — the full text of “Finding Rows With No Match (Anti-Join)” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Finding Rows With No Match (Anti-Join)”?

The LEFT JOIN / IS NULL pattern for finding orphans and missing data. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Finding Rows With No Match (Anti-Join)” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Interview Prep