Example 1
Find customers who have never placed an order
Alice has two orders and Carol has one, so only Bob — who has no orders at all — appears in the result. The LEFT JOIN produces a row for every customer including those with no orders; for unmatched customers the orders columns are all NULL. The WHERE o.customer_id IS NULL filter then keeps only those unmatched rows. Prefer this pattern over NOT IN (SELECT ...) because NOT IN returns no rows at all if any value in the subquery is NULL.
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, amount INT);
INSERT INTO
customers (id, name)
VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Carol');
INSERT INTO
orders (id, customer_id, amount)
VALUES
(1, 1, 100),
(2, 1, 250),
(3, 3, 75);Validated query Shared across supported engines.
SQL
SELECT
c.id,
c.name
FROM
customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE
o.customer_id IS NULL
ORDER BY
c.id;Expected result Returned rows for the shared example.
| id | name |
|---|---|
| 2 | Bob |
The SQL is identical across all supported engines.