Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Joins and Subqueries

SQL anti-joins

Learn the SQL anti-join: find rows with no match in another table using NOT EXISTS, NOT IN or LEFT JOIN with IS NULL, and the NULL trap that breaks NOT IN.

An anti-join returns the rows of one table that have no matching row in another table. Typical questions: which customers never ordered, which books never sold, which employees are not assigned to any project. SQL has no ANTI JOIN keyword. You write it with one of three patterns, and they are not equally safe.

The counterpart is a semi-join, "rows that have at least one match", which EXISTS and IN express. An anti-join is its negation.

SQL
-- the three patterns, same question
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.book_id = b.id)
WHERE b.id NOT IN (SELECT book_id FROM orders)
LEFT JOIN orders o ON o.book_id = b.id WHERE o.id IS NULL

Try it

SQL NOT EXISTS: books that were never ordered ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL LEFT JOIN ... IS NULL: the same result ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL NOT IN breaks when the list contains NULL ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The three patterns

NOT EXISTS

The correlated subquery looks for any matching row; NOT EXISTS keeps the outer row if there is none. It reads like the question, handles NULL correctly and lets the optimizer use an index on the inner join column. This is the recommended default.

LEFT JOIN with IS NULL

Join everything, then keep only the rows where the right side did not match. Test a column that cannot be NULL when a match exists, normally the right table's primary key (o.id IS NULL). Do not test a column that might itself be NULL in matching rows. Be careful with the filter location: a condition on the right table in WHERE turns the join back into an inner join, so conditions on that table belong in ON.

NOT IN

Compact and readable, but dangerous. If the subquery returns any NULL, the comparison x <> NULL is unknown for every row that does not match another value, and NOT IN returns no rows at all. In the third example the naive count is 0 because the CEO's manager_id is NULL. If you use NOT IN, filter inside the subquery with IS NOT NULL, or make sure the column is NOT NULL.

Dialect differences

All three patterns work in SQLite, PostgreSQL, MySQL, SQL Server and Oracle. Performance differs: PostgreSQL and SQL Server plan NOT EXISTS and LEFT JOIN ... IS NULL as true anti-joins; older MySQL versions and Oracle may plan NOT IN poorly, particularly when NULLs are possible, since the engine must prove there are none.

Usage notes

  • Prefer NOT EXISTS. It is NULL-safe, clear and fast with an index on the foreign key.
  • Anti-join versus EXCEPT. EXCEPT compares whole rows or keys from two queries and removes duplicates. An anti-join keeps the full row of the left table.
  • Duplicates. The three patterns return each left row once; LEFT JOIN anti-joins do so because unmatched rows appear exactly once.
  • Orphan checks. An anti-join from a child to its parent finds rows that violate a foreign key you forgot to declare.
  • Index the lookup. Put an index on the inner column, for example orders(book_id).