Finding rows with no match in SQL

The anti join: an outer join, then keep only the rows that failed to match.

SELECT
    c.id,
    c.name
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.id
WHERE o.id IS NULL;

SELECT
    c.id,
    c.name
FROM customers AS c
WHERE NOT EXISTS (
        SELECT 1
        FROM orders AS o
        WHERE o.customer_id = c.id
    );

How it works

1. LEFT JOIN fills unmatched rows with NULLs on the right.
2. WHERE right.key IS NULL keeps exactly those unmatched rows.
3. NOT EXISTS says the same thing and usually plans the same way.

Keywords and builtins used here

• AS
• EXISTS
• FROM
• IS
• JOIN
• LEFT
• NOT
• NULL
• ON
• SELECT
• WHERE
• c

The run, in numbers

Lines

17

Characters to type

221

Tokens

64

Three-star pace

85 tpm

At the three-star pace of 85 tokens a minute, this run takes about 45 seconds.

Type this snippet

Step 8 of 9 in Joins, step 16 of 22 in Joins & aggregation.

← Previous Next →