Left Join to Find Customers With No Orders — SQL

Solving tips

To find rows on the left with no match on the right, LEFT JOIN then filter WHERE right_key IS NULL.
Test the IS NULL on a right-side column that can never legitimately be NULL, such as the right table's primary key.
This anti-join pattern answers who is missing, unlike an INNER JOIN which answers who matches.

Find the customers who have never placed an order.

Schema

CREATE TABLE customers (
    id   INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
    id          INT PRIMARY KEY,
    customer_id INT REFERENCES customers(id),
    amount      NUMERIC(10,2) NOT NULL
);

customers

id	name
1	Alice
2	Bob
3	Carol
4	Dave
5	Eve

orders

id	customer_id	amount
101	1	50.00
102	1	30.00
103	3	20.00
104	4	80.00

Task

Return the customers who appear in no order, with columns customer_id (the customer’s id) and name. Order by customer_id ascending.

Expected output

customer_id	name
2	Bob
5	Eve

Approach

LEFT JOIN keeps every customer and fills the order columns with NULLs when no order matches. Filtering WHERE o.customer_id IS NULL (or any non-nullable order column) keeps exactly the customers that failed to match, giving an anti-join.

Query

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

Walkthrough

The LEFT JOIN produces every customer row; Alice matches two orders, Carol and Dave match one each, so their o.customer_id is populated.
Bob and Eve have no matching order, so the joined order columns come out NULL.
WHERE o.customer_id IS NULL discards Alice, Carol, and Dave and keeps Bob and Eve.
ORDER BY c.id returns them as customer 2 (Bob) then customer 5 (Eve).

Complexity & notes

An index on orders.customer_id lets the planner probe efficiently for each customer; without it this is a hash left join over full scans.
Pitfall: applying the filter in the ON clause instead of WHERE. ON o.customer_id = c.id AND o.customer_id IS NULL changes the join condition rather than filtering unmatched rows and would return nothing meaningful. The IS NULL test must live in WHERE.
Equivalent formulations are NOT EXISTS and NOT IN, but NOT IN is unsafe when the subquery can yield NULLs (see the anti-join problem).