0Pricing
SQL Interview Prep · Lesson

Finding Rows With No Match (Anti-Join)

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

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.

All lessons in this course

  1. LEFT JOIN and Preserving Unmatched Rows
  2. RIGHT and FULL OUTER JOIN Semantics
  3. Finding Rows With No Match (Anti-Join)
  4. The WHERE-on-Outer-Join Trap
← Back to SQL Interview Prep