Customers With No Orders

SQLAnti-joinMidSeniorUpdated Nov 10, 2026

Return every customer who has never placed an order. Note that orders.customer_id is nullable because guest checkouts are stored in the same table.

Schema

CREATE TABLE customers (
  id    BIGINT PRIMARY KEY,
  email TEXT NOT NULL
);

CREATE TABLE orders (
  id          BIGINT PRIMARY KEY,
  customer_id BIGINT,  -- nullable: guest checkouts also live here
  total       NUMERIC NOT NULL
);

Drill This: Your Query First

Answer, Then Reveal

Write the query before you look at the answer.

Skip to Reading

The Correct Query

Option (b)

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

LEFT JOIN ... WHERE o.id IS NULL is the textbook anti-join and is NULL-safe. Option (a) is the classic NULL trap: NOT IN against a subquery that contains any NULL returns no rows because 'x NOT IN (..., NULL, ...)' evaluates to UNKNOWN. (c) is logically impossible since INNER JOIN guarantees o.id is not null. (d) just checks whether any guest order exists anywhere, it has no correlation to c.

The Options That Fail

The explanation above says why each of these breaks.

Option (a)

SELECT c.id, c.email
FROM customers c
WHERE c.id NOT IN (SELECT customer_id FROM orders);

Option (c)

SELECT c.id, c.email
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Option (d)

SELECT c.id, c.email
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id IS NULL);