Categories That Have Products — Subqueries in WHERE in SQL

The problem

Brightlane's catalogue team is auditing category utilisation and needs to identify categories that have at least one product assigned to them.

Write a query to return the category ID and name for every utilised category.

Assumptions:

The categories table contains every defined category.
The products table contains every product; category_id identifies the category.
A category with many products appears once in the result.

Output:

One row per utilised category, 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
  categories
WHERE
  id IN (
    SELECT
      category_id
    FROM
      products
  )

The shape

The set of category IDs that are actually being used lives inside products, not categories. IN (subquery) pulls that set out of products and uses it to filter the categories rows.

Clause by clause

SELECT id, name FROM categories reads every defined category, including ones that may have no products. The filter trims it to the categories that are actually in use.
WHERE id IN (SELECT category_id FROM products) is the membership test. PostgreSQL runs the inner query, collects every category_id across products into a set, and keeps an outer category row only when its id appears in that set. A category with ten products contributes its id ten times to the inner set; the outer row still passes exactly once. That's why the ten utilised categories come back as ten rows even though they cover dozens of products between them.

Why this and not the other direction

The same membership question can run either direction in this schema. The audit asks about categories, so the outer reads from categories and the subquery produces the set from products. Flipping the question to "which products belong to a defined category" would swap the two tables. The operator works whichever way the question is framed.

You practiced IN with a subquery from the dimension side rather than the fact side. The shape is symmetric: filter customers by membership in the orders table, or filter categories by membership in the products table — the operator works either direction.

Return the category ID and name for every utilised category

The shape

Clause by clause

Why this and not the other direction

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