Customers Without Matching Orders — NULL Handling in Joins in SQL

The problem

Brightlane's growth team is building a re-engagement campaign and needs to identify customers who have never placed an order.

Write a query to return the name and email of every customer with no order history.

Assumptions:

The customers table has one row per customer with a name and an email.
The orders table has one row per order, linked to a customer by customer_id.
A customer with no orders on file is considered to have no order history.

Output:

One row per qualifying customer, with columns name and email.

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
  c.name,
  c.email
FROM
  customers c
  LEFT JOIN orders o ON c.id = o.customer_id
WHERE
  o.id IS NULL

The shape

The anti-join shape — LEFT JOIN followed by WHERE o.id IS NULL — returns the customers the orders table has nothing to say about. The LEFT JOIN keeps every customer and attaches an order when one exists; the WHERE then keeps only the customers whose attached order is missing, which is exactly the no-order-history population.

Clause by clause

SELECT c.name, c.email returns the two display columns the re-engagement campaign needs. Only customer-side columns appear, because the order-side columns will all be missing for the rows that survive the filter.
FROM customers c LEFT JOIN orders o ON c.id = o.customer_id pairs each customer with their orders. A customer with one or more orders contributes one row per order; a customer with no orders contributes a single row where every o.* column is missing.
WHERE o.id IS NULL keeps only the rows whose attached order is missing. A real order always has a non-missing id, so this condition is true exactly for the unmatched customers. The eight customers in the result — Omar Jensen, Mark Hayes, Nina Irwin, and the rest — are the ones with no order history.

The trap

The filter has to be on a column the right table guarantees is non-missing for real rows. o.id is a primary key, so it is non-missing for every actual order and missing for every unmatched row. Filtering on a column that can legitimately be missing — like a status or a note — would drop real orders alongside the unmatched ones.

You practiced the anti-join shape — LEFT JOIN then WHERE right.id IS NULL to surface left-side records with no match on the right.

Return the name and email of every customer with no order history

The shape

Clause by clause

The trap

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