Lesson 4 of 15 · 15 min
Finding missing records: anti-joins
By the end of this lesson you can write an anti-join with LEFT JOIN … IS NULL to find rows that have no match in another table, choose the right column to test, and recognise the equivalent NOT EXISTS form.
In the last lesson a LEFT JOIN kept customers without orders and filled the order columns with NULL. Now turn that around: if unmatched rows are the ones with NULL in the right table's key, then keeping only those rows gives you exactly the records that have no match.
This pattern is called an anti-join: return rows from A that have no matching row in B. It is written in two steps:
LEFT JOINthe table that might be missing, so every row of A is kept.WHERE b.primary_key IS NULLkeeps only the rows where no match was found.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons