Customers Who Never Ordered

Return the customers who have never placed an order. The orders table intentionally contains a row with a NULL customer, which is what makes the naive NOT IN approach fail.

Schema

CREATE TABLE customers (
    id   integer PRIMARY KEY,
    name text    NOT NULL
);

CREATE TABLE orders (
    id          integer PRIMARY KEY,
    customer_id integer,        -- nullable: an order can be unattributed
    amount      integer NOT NULL
);

Sample data — customers:

Sample data — orders:

Task

Return the name of every customer who has no matching row in orders. Order by name ascending. Expected column: name.

Expected output

Ana (id 1) and Cy (id 3) have orders; Ben and Dee do not. The NULL customer on order 104 belongs to nobody and must not suppress the answer.

Approach

This is an anti-join: keep customers with no matching order. NOT EXISTS expresses it safely because the correlated subquery only ever yields “there is a matching row” (true) or “there is not” (false). NULLs inside orders never turn the predicate into unknown, which is the weakness of the NOT IN form here.

flowchart TD
    A[Anti-join: customers with no order] --> B{Which operator?}
    B -->|NOT IN, subquery has NULL| C[Returns zero rows: trap]
    B -->|NOT IN, no NULLs| D[Correct]
    B -->|NOT EXISTS| E[Correct regardless of NULLs: safe]

Query

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

Walkthrough

For each customer, the subquery searches orders for a row whose customer_id equals that customer’s id. Ana (id 1) matches orders 101 and 102, so NOT EXISTS is false and she is excluded; Cy (id 3) matches order 103 and is likewise excluded. Ben (id 2) and Dee (id 4) have no matching order, so NOT EXISTS is true and they are kept. Order 104’s NULL customer never equals any c.id, so it simply never matches anyone and does no harm. Sorting by name gives Ben, then Dee.

Complexity & notes

The equivalent positive query — customers who did order — can be written with either IN or EXISTS, and both are correct and usually planned as a semi-join:

-- both return Ana and Cy
SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders);
SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

The trap is the negated NOT IN form:

-- returns ZERO rows, not Ben/Dee, because of the NULL customer_id
SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

Because the subquery list contains a NULL, id NOT IN (1, 3, NULL) evaluates to unknown (never true) for every customer, so the query silently returns nothing. Fixes are to use NOT EXISTS (preferred), to filter the NULL out with WHERE customer_id IS NOT NULL inside the subquery, or to use a LEFT JOIN ... WHERE o.id IS NULL anti-join. Performance-wise, an index on orders(customer_id) makes the NOT EXISTS lookup efficient.