Anti-Joins and Semi-Joins Explained

1. What sets anti-joins and semi-joins apart

A semi-join returns every row of the left table for which at least one matching row exists in the right table, but never outputs columns of the right table in the result and never duplicates a row of the left table, even if several matching rows exist on the right. An anti-join is the exact opposite: it returns every row of the left table for which not a single matching row exists in the right table.

Neither pattern is its own keyword in standard SQL, both are expressed through constructs like EXISTS, NOT EXISTS, IN, NOT IN, or a LEFT JOIN followed by an IS NULL condition. The term semi-join or anti-join comes from relational algebra and describes how the database optimizer handles these constructs internally, regardless of the concrete SQL syntax used to write them.

The practical value of both patterns lies in questions like "which customers have never ordered" for the anti-join or "which products have at least one review" for the semi-join. An ordinary JOIN would either return duplicated rows or produce the wrong result for these questions, which is why the correct choice between anti-join and semi-join, and their respective implementation, is decisive for the query's correctness.

2. Semi-join with EXISTS

The most robust implementation of a semi-join is a correlated subquery with EXISTS. EXISTS checks for every row of the outer query whether the inner query returns at least one row, and stops evaluating the inner query as soon as the first matching row is found. This short-circuit evaluation makes EXISTS very efficient with correct indexing, because never more than one matching row per outer row actually has to be read.

A semi-join with EXISTS is also robust against NULL values in the correlated column: the subquery inside EXISTS does not need to return specific columns, in practice SELECT 1 is often written, because only the existence of a row is checked, not its content. This property fundamentally distinguishes EXISTS from IN, which compares actual values and is sensitive to NULL in the process.

-- Semi-join: products that have at least one review, using EXISTS
SELECT p.product_id, p.product_name
FROM products p
WHERE EXISTS (
    SELECT 1
    FROM reviews r
    WHERE r.product_id = p.product_id
);

3. Semi-join with IN and its limits

An alternative, often perceived as more intuitive, implementation of a semi-join is WHERE column IN (SELECT column FROM other_table). For many modern optimizers, especially in PostgreSQL and SQL Server, IN with a subquery is in practice semantically equivalent to EXISTS and is often internally translated into the same execution plan, as long as the subquery cannot contain NULL values in the relevant column.

The decisive difference shows up once several columns or more complex correlation conditions come into play: IN only compares a single scalar value or a tuple against a fixed list, while EXISTS allows an arbitrarily complex condition in the subquery's WHERE clause, for example comparing several columns or additional filter criteria like a review date. For simple single-column comparisons without NULL risk, IN is a legitimate, often even more readable choice.

-- Semi-join with IN: simple, readable for a single-column comparison
SELECT p.product_id, p.product_name
FROM products p
WHERE p.product_id IN (
    SELECT product_id FROM reviews
);

-- EXISTS is required once the condition needs more than one column
SELECT p.product_id, p.product_name
FROM products p
WHERE EXISTS (
    SELECT 1
    FROM reviews r
    WHERE r.product_id = p.product_id
      AND r.rating >= 4
      AND r.created_at >= CURRENT_DATE - INTERVAL '90 days'
);

4. Anti-join with NOT EXISTS

The anti-join with NOT EXISTS is the direct counterpart to EXISTS and in practice the recommended default solution for the question of rows without a match. NOT EXISTS returns exactly the rows of the outer query for which the correlated subquery finds not a single row, with the same short-circuit evaluation and the same robustness against NULL values as EXISTS.

This robustness against NULL is the main reason why NOT EXISTS is almost always the safer choice over the seemingly equivalent NOT IN variant. While NOT IN lets a single NULL value in the subquery's result set collapse the entire outer query's result to empty, explained in detail in the section on the NULL trap, NOT EXISTS remains completely unaffected by this problem.

-- Anti-join: customers who have never placed an order, using NOT EXISTS
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

5. Anti-join with LEFT JOIN and IS NULL

A second common implementation of the anti-join is a LEFT JOIN between both tables, followed by a WHERE condition checking for IS NULL on a column of the right table, typically the primary key. A LEFT JOIN keeps every row of the left table, even without a matching row on the right, and fills the right columns with NULL in that case. The subsequent IS NULL condition filters exactly for those rows without a match.

This pattern is functionally equivalent to NOT EXISTS, as long as the condition checks a column that can never be NULL on real matches, such as a primary key or a NOT NULL foreign key column. A common, subtle mistake is checking a column that can also be NULL on real matches, for example an optional field, which then wrongly reports rows with a match as rows without one.

-- Anti-join: same result as NOT EXISTS, using LEFT JOIN and IS NULL
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.customer_id IS NULL;

-- Wrong: checking a nullable column instead of the join key
-- can wrongly classify matched rows as "no match"
-- WHERE o.shipped_at IS NULL   -- shipped_at may be NULL even with a real order

6. The NULL trap with NOT IN

NOT IN is the most dangerous of the four implementations for an anti-join, because its behavior with NULL values in the subquery is counterintuitive and regularly leads to wrong, empty results in practice. The reason lies in SQL's three-valued logic: comparing a row against NULL returns neither true nor false, but unknown. As soon as the subquery result set of NOT IN contains even a single NULL value, the entire NOT IN condition evaluates to unknown for every single outer row, which is treated like false in a WHERE clause.

The result is a query that silently returns an empty result set without any error, even though numerous rows should meet the condition from a business perspective. This mistake is particularly insidious because it stays hidden when testing with clean test data without NULL values, and only surfaces in production once a single NULL value lands in the affected column, for example through an incompletely filled foreign key.

-- Dangerous: NOT IN silently returns zero rows if the subquery has NULLs
SELECT c.customer_id, c.name
FROM customers c
WHERE c.customer_id NOT IN (
    SELECT customer_id FROM orders  -- if this contains one NULL, the whole
                                     -- NOT IN evaluates to unknown for every row
);

-- Safe alternative producing the correct result regardless of NULLs
SELECT c.customer_id, c.name
FROM customers c
WHERE c.customer_id NOT IN (
    SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);

7. Performance: NOT EXISTS versus LEFT JOIN IS NULL

With correct indexing of the foreign key column, modern optimizers in PostgreSQL, SQL Server, MySQL 8, and Oracle generally produce identical or nearly identical execution plans for NOT EXISTS and LEFT JOIN ... IS NULL, both are internally executed as an anti-join operation, visible in EXPLAIN ANALYZE as Anti Join in PostgreSQL, for example. The choice between the two syntax forms on modern databases is therefore primarily a question of readability, not performance.

A difference remains once several conditions on the right table are involved: a LEFT JOIN with additional filter criteria in the ON clause versus criteria in the WHERE clause changes the semantics fundamentally and is a common source of bugs. NOT EXISTS with all conditions inside the subquery is less error prone in such cases, because the entire filter logic stays bundled in one place instead of being spread across ON and WHERE.

8. Practical examples from everyday database work

A typical semi-join use case is finding all products that appear at least once in an order, for example to check inventory only for actually sold products. A typical anti-join use case is finding orphaned records during data cleanup, for example rows in a detail table whose referenced record in the main table has since been deleted, a scenario that can occur despite foreign key constraints through historical data imports without consistent referential integrity.

Another practical example is reconciling two systems during a migration: an anti-join finds all records in the source system that do not yet exist in the target system, matched on the unique ID shared by both systems. This task is often combined with a second anti-join in the opposite direction, to also find records that exist in the target system but are missing in the source system, which together produce a complete, symmetric difference analysis between both systems.

9. Common mistakes with anti- and semi-joins

By far the most common and most dangerous mistake is using NOT IN with a subquery that can potentially return NULL values, as described in the section on the NULL trap. This mistake should be flagged immediately in every code review and replaced with NOT EXISTS, regardless of whether the current test data happens to contain no NULL values.

A second mistake is using an ordinary INNER JOIN instead of a semi-join when the right table can contain multiple matching rows per left row. An INNER JOIN duplicates the left row for every match in that case, which leads to incorrectly high sums in a subsequent aggregation, while a semi-join with EXISTS guarantees every left row appears only once, regardless of how many matches exist on the right.

10. Summary

A semi-join finds rows with at least one match, an anti-join finds rows with no match at all, both without pulling columns of the second table into the result or duplicating rows. EXISTS is the most robust implementation for the semi-join, NOT EXISTS the most robust for the anti-join, both with short-circuit evaluation and full safety against NULL values in the correlated column.

LEFT JOIN with IS NULL on the join key is an equivalent alternative to NOT EXISTS, as long as the check does not accidentally target a nullable column of the right table. NOT IN should only be used for an anti-join together with an explicit IS NOT NULL filter in the subquery, since a single NULL value otherwise silently collapses the entire result to empty.

11. FAQ: Anti-Joins and Semi-Joins