Customers Who Placed an Order — Subqueries in WHERE in SQL

The problem

Brightlane's CRM team is building an active-buyer segment for a promotional campaign and needs to identify customers who have placed at least one order.

Write a query to return the customer ID and name for every customer whose id appears 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 multiple orders appears once in the result.

Output:

One row per active buyer, 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 IN (
    SELECT
      customer_id
    FROM
      orders
  )

The shape

The set of allowed customer IDs is itself a query result. IN (subquery) lets the inner SELECT customer_id FROM orders produce that set on the fly, and the outer WHERE keeps only customers whose id shows up in it.

Clause by clause

SELECT id, name FROM customers reads every customer Brightlane has on file. This is the candidate set the WHERE will narrow.
WHERE id IN (SELECT customer_id FROM orders) is the membership test. PostgreSQL runs the inner query first, collects every customer_id value in orders into a set, and keeps an outer row only when its id appears in that set. A customer with three orders contributes their id three times to the inner set, but the outer row still passes once. The test is membership, not count, so multiplicity on the inner side doesn't change the answer.

Why this and not `IN (1, 2, 3, ...)`

The literal-list form of IN is the same operator with a hardcoded set. The subquery form is what you reach for the moment the set isn't known ahead of time, and the customer-IDs-with-orders set never is. Hardcoding it would mean rewriting the query every time a new order lands. The subquery keeps the set live: whatever orders currently contains is what the membership check uses.

You practiced IN with a subquery — the same operator as IN with a literal list, but the set of allowed values comes from a query result. The recurring shape any time the membership set is itself a query.

Return the customer ID and name for every customer whose `id` appears in the orders table

The shape

Clause by clause

Why this and not `IN (1, 2, 3, ...)`

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 (1, 2, 3, ...)

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

Why this and not `IN (1, 2, 3, ...)`