Anti-joins and Semi-joins

Rows with no match and rows with at least one: LEFT JOIN ... IS NULL, NOT EXISTS, NOT IN, and what MySQL 8.4 does with each.

SyntaxSELECT ... FROM a LEFT JOIN b ON a.id = b.a_id WHERE b.a_id IS NULL

A semi-join returns rows of the left table that have at least one match on the right, without duplicating them and without returning any right-hand columns. An anti-join returns the rows with no match. Neither is a keyword — they are shapes you write, and names MySQL uses internally for the plans it builds.

The spellings

An anti-join can be written three ways: LEFT JOIN … WHERE b.key IS NULL, WHERE NOT EXISTS (SELECT 1 …), or WHERE k NOT IN (SELECT …). A semi-join is WHERE EXISTS (…) or WHERE k IN (SELECT …) — and note that an ordinary INNER JOIN is not equivalent, because it duplicates the left row once per match and needs a DISTINCT to compensate.

What the optimizer does

The optimizer has transformed IN and = ANY subqueries into semi-joins since long before 8.0; EXISTS was brought under the same transformation in MySQL 8.0.16, and since 8.0.17 NOT IN and NOT EXISTS are transformed into anti-joins. All of it applies in 8.4 and is controlled by the semijoin flag of optimizer_switch. It then picks a strategy — table pullout, duplicate weedout, FirstMatch, LooseScan or materialization — by cost. So NOT EXISTS and the LEFT JOIN … IS NULL form usually reach the same plan; prefer NOT EXISTS for clarity and because it is immune to the NULL trap below.

Example

-- Anti-join: customers who have never placed an order.
-- Test a column of the right table that is declared NOT NULL, so
-- IS NULL can only mean 'the join found nothing'.
SELECT  c.id,
        c.email,
        c.created_at
FROM    customers AS c
LEFT JOIN orders AS o
        ON o.customer_id = c.id
WHERE   o.customer_id IS NULL      -- the anti-join test
ORDER BY c.created_at;

-- The same question as NOT EXISTS. Same answer, usually the same
-- plan (MySQL 8.0.17+ transforms NOT EXISTS into an antijoin), and
-- it states the intent without relying on a NULL side effect.
SELECT  c.id, c.email, c.created_at
FROM    customers AS c
WHERE   NOT EXISTS (
            SELECT 1 FROM orders AS o
            WHERE  o.customer_id = c.id
        )
ORDER BY c.created_at;

When to use it

List customers, users or accounts that have never performed an action.
Find products with no sales in a period so they can be delisted.
Check which rows from an import file are missing from the target table.
Select parents that have at least one child without duplicating the parent row.

More examples

The NOT IN NULL trap

The classic silent-empty-result bug, usually discovered when a nightly 'dormant customers' job stops sending anything. Treat NOT IN over a subquery as safe only when the subquery column is declared NOT NULL, and make NOT EXISTS the default habit.

-- If orders.customer_id is nullable and a single row has NULL there,
-- this returns ZERO rows -- not an error, not a warning.
-- NOT IN yields NULL (not TRUE) when no match is found and the list
-- contains a NULL, and WHERE keeps only TRUE.
SELECT c.id, c.email
FROM   customers AS c
WHERE  c.id NOT IN (SELECT o.customer_id FROM orders AS o);

-- Patch: exclude the NULLs explicitly.
SELECT c.id, c.email
FROM   customers AS c
WHERE  c.id NOT IN (
           SELECT o.customer_id FROM orders AS o
           WHERE  o.customer_id IS NOT NULL
       );

-- Better: NOT EXISTS is unaffected, because it asks whether any row
-- satisfies the predicate rather than comparing against a value list.
SELECT c.id, c.email
FROM   customers AS c
WHERE  NOT EXISTS (
           SELECT 1 FROM orders AS o WHERE o.customer_id = c.id
       );

Semi-join versus a plain INNER JOIN

Use the EXISTS form whenever you need existence rather than data from the other table: it expresses the intent, cannot duplicate rows, and lets the optimizer stop scanning after the first hit. The DISTINCT version gives the same rows but asks the server to build and dedup a wider intermediate result first.

-- WRONG shape: a customer with 12 orders appears 12 times.
SELECT c.id, c.email
FROM   customers AS c
JOIN   orders AS o ON o.customer_id = c.id;

-- Works, but DISTINCT forces a dedup pass over a widened result set.
SELECT DISTINCT c.id, c.email
FROM   customers AS c
JOIN   orders AS o ON o.customer_id = c.id;

-- Semi-join: stops at the first matching order per customer.
-- MySQL 8.0.16+ transforms EXISTS and IN into a semijoin and picks
-- a strategy (FirstMatch, LooseScan, materialization, ...) by cost.
SELECT c.id, c.email
FROM   customers AS c
WHERE  EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.id);

An anti-join with extra conditions, and reading the plan

The realistic anti-join: 'no matching row satisfying these conditions', which the LEFT JOIN spelling would force you to spread across an ON clause and a WHERE IS NULL. The EXPLAIN line is how you verify the optimizer produced an antijoin rather than executing the subquery once per outer row.

-- Products with no sale in the last 90 days. The date test is part of
-- the existence question, so it lives inside NOT EXISTS.
SELECT p.id, p.sku, p.list_cents
FROM   products AS p
WHERE  p.discontinued = 0
  AND  NOT EXISTS (
           SELECT 1
           FROM   order_items AS oi
           JOIN   orders      AS o ON o.id = oi.order_id
           WHERE  oi.product_id = p.id
             AND  o.placed_at  >= CURRENT_DATE - INTERVAL 90 DAY
       );

-- Confirm the transformation happened.
-- FORMAT=TREE shows 'Hash antijoin' or a nested-loop antijoin;
-- traditional EXPLAIN shows 'Using where; Not exists' for the
-- LEFT JOIN ... IS NULL spelling.
EXPLAIN FORMAT=TREE
SELECT p.id FROM products AS p
WHERE NOT EXISTS (SELECT 1 FROM order_items AS oi WHERE oi.product_id = p.id);

Discussion

Be the first to comment on this lesson.

The spellings

What the optimizer does

Example

When to use it

More examples

The NOT IN NULL trap

Semi-join versus a plain INNER JOIN

An anti-join with extra conditions, and reading the plan

Related

Discussion