Customers Who Have Ordered — Correlated Subqueries in SQL

The problem

Brightlane's marketing team wants to contact every customer who has made at least one purchase.

Write a query to return the ID and name of every customer who has placed at least one order.

Assumptions:

The customers table has one row per customer with an id and a name.
The orders table has one row per order, linked to a customer by customer_id.
A qualifying customer has one or more orders linked to that customer_id.

Output:

One row per qualifying customer, 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
  c.id,
  c.name
FROM
  customers c
WHERE
  EXISTS (
    SELECT
      1
    FROM
      orders o
    WHERE
      o.customer_id = c.id
  )

The shape

This is the canonical \EXISTS\ shape. For each customer, the inner query checks whether any order references that customer's \id\. \EXISTS\ returns true the moment it finds the first match and stops, so the outer \WHERE\ keeps exactly the customers who have placed at least one order.

Clause by clause

\SELECT c.id, c.name\ returns the two columns the spec asks for, taken straight from the \customers\ table without any aggregation.
\FROM customers c\ reads every customer record. The alias \c\ is what the inner subquery references.
\WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)\ is the correlated filter. The inner \o.customer_id = c.id\ references the outer customer row, so the subquery checks "does this specific customer have any orders?" The \SELECT 1\ is conventional; \EXISTS\ only cares whether the subquery returns any row, never about which column or which value, so a constant is the cleanest thing to project.

Why this and not \`IN\` or \`INNER JOIN\`

\WHERE c.id IN (SELECT customer_id FROM orders)\ returns the same customers. \EXISTS\ is usually preferred for "at least one match exists" because it short-circuits on the first hit and because \IN\ has surprising NULL semantics if the inner column is ever nullable. An \INNER JOIN\ to \orders\ plus \DISTINCT\ also works but multiplies rows during the join only to collapse them back, which is more work than the question requires.

You practiced WHERE EXISTS (SELECT 1 FROM ... WHERE inner.fk = outer.pk) — the canonical correlated semi-join shape; the outer record is kept if at least one related record matches, with no row multiplication.

Return the ID and name of every customer who has placed at least one order

The shape

Clause by clause

Why this and not \`IN\` or \`INNER JOIN\`

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

The shape

Clause by clause

Why this and not \IN\ or \INNER JOIN\

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

Why this and not \`IN\` or \`INNER JOIN\`