Example 1
List customers who do not have any orders
Ada and Cara both have at least one order, so their joined rows contain a non-NULL o.id and are filtered out. Bob has no matching order row, so the right side stays NULL and Bob is returned.
Source table data Rows loaded before the example query runs.
Setup
CREATE TABLE customers (id INT, name VARCHAR(50));
CREATE TABLE orders (id INT, customer_id INT);
INSERT INTO
customers (id, name)
VALUES
(1, 'Ada'),
(2, 'Bob'),
(3, 'Cara');
INSERT INTO
orders (id, customer_id)
VALUES
(101, 1),
(102, 1),
(103, 3);Validated query Shared across supported engines.
SQL
SELECT
c.id,
c.name
FROM
customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE
o.id IS NULL
ORDER BY
c.id;Expected result Returned rows for the shared example.
| id | name |
|---|---|
| 2 | Bob |
All engines use the same anti-join pattern: `LEFT JOIN` plus a `NULL` check on the joined table.