Customers with No Purchases — Subqueries in WHERE in SQL

The problem

Brightlane's reactivation campaign targets customers who have never placed an order.

Write a query to return the customer ID and name for every customer with no purchase history.

Assumptions:

The customers table contains every customer Brightlane has on file.
The orders table contains every order; customer_id identifies the buyer.
Brightlane's orders.customer_id column is never missing — every order is tied to a real customer.

Output:

One row per customer with no orders, with columns id and name.

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,
  name
FROM
  customers
WHERE
  id NOT IN (
    SELECT
      customer_id
    FROM
      orders
  )

The shape

NOT IN (subquery) inverts the membership test. The inner query collects every customer_id that has placed an order, and the outer keeps customers whose id is not in that set. The eight result rows are customers Brightlane has on file with zero orders against their name.

Clause by clause

SELECT id, name FROM customers reads every customer on file. The filter trims this to the ones that have never appeared in orders.
WHERE id NOT IN (SELECT customer_id FROM orders) runs the inner query first, builds the set of all customer_id values across orders, and keeps an outer customer row only when its id is absent from that set. Customers 63 through 70 come through — none of them have an order recorded against their id.

The trap

NOT IN is safe here only because the assumptions guarantee orders.customer_id is never missing. If even one row in orders had a NULL in customer_id, this query would return zero rows. Not eight, not most-of-eight, zero.

The reason is three-valued logic. PostgreSQL can't confirm that customer 63's id is "not equal to NULL" because comparisons with NULL are undefined. The NOT IN test collapses to NULL for every outer row, the WHERE treats NULL as falsy, and no row passes.

The fix when the inner column might be nullable is to add WHERE customer_id IS NOT NULL to the subquery, or to rewrite the negation as NOT EXISTS. Brightlane's schema is clean, so the bare NOT IN works here. The same shape against a nullable column is one of the most common silent-zero-rows bugs in SQL.

You practiced NOT IN with a subquery and saw it produce the right answer because the inner column has no missing values. The recurring shape any time a presence/absence question can be expressed by membership in a set.

Return the customer ID and name for every customer with no purchase history

The shape

Clause by clause

The trap

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