Customers Who Never Ordered — UNION in SQL

The problem

Brightlane's retention team wants to identify customers who have never placed an order.

Write a query to return the customer ID for every customer who does not appear in the orders table.

Assumptions:

The customers table contains every customer Brightlane has on file.
The orders table contains every order; customer_id identifies the buyer.
A customer with no orders is one whose id does not appear anywhere in orders.customer_id.

Output:

One row per customer with no orders, with a single column customer_id.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  id AS customer_id
FROM
  customers
EXCEPT
SELECT
  customer_id
FROM
  orders

The shape

EXCEPT returns rows from the left query that don't appear in the right query. Here, that's the 8 customer IDs in customers that never show up as a customer_id in orders — the retention team's never-purchased list, computed directly as a set difference.

Clause by clause

SELECT id AS customer_id FROM customers is the left input. The id column is renamed to customer_id so both sides project a column with the same role, even though the underlying column names differ across the two tables. The output column name comes from the left query, which is why the alias goes here.
EXCEPT is the set operator. PostgreSQL evaluates both queries, deduplicates each side, then returns rows from the left with no match on the right.
SELECT customer_id FROM orders is the right input. A customer with five orders contributes five rows here, but the deduplication inside EXCEPT collapses them — the customer gets removed from the left exactly once.

The trap

EXCEPT is directional. Writing orders EXCEPT customers answers a different question: customer IDs in orders not in customers — orphan order rows, a referential integrity check rather than a retention list. The query order is the answer. Get it backwards and the result still runs cleanly; it just answers something the team didn't ask.

You practiced EXCEPT for set-difference logic. The recurring rule: EXCEPT is directional — A EXCEPT B returns rows in A not in B, and swapping the queries returns a completely different set.

Return the customer ID for every customer who does not appear in the orders table

The shape

Clause by clause

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.