Categories That Have Products — Query Performance in SQL

The problem

Scenario: Brightlane's catalog team is verifying which product categories are actively used across the catalog — categories with at least one assigned product.

Task: Write a query to return the id and name of every category that has at least one product assigned to it.

Assumptions:

The result covers only categories that appear as the category_id of at least one product.

Output:

One row per qualifying category.
Columns in this order: category_id, category_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 AS category_id,
  c.name AS category_name
FROM
  categories c
WHERE
  EXISTS (
    SELECT
      1
    FROM
      products p
    WHERE
      p.category_id = c.id
  )

The shape

The question is existence, not value. For each category, is there at least one product on file? EXISTS answers that with a correlated subquery — return the category as soon as one matching product is found, and stop looking.

Clause by clause

SELECT c.id AS category_id, c.name AS category_name returns the identifying columns from the parent side.
FROM categories c is the parent set. Every category is a candidate until the existence check filters it.
WHERE EXISTS (...) keeps a category only when the inner subquery returns at least one row. The subquery returns rows whenever a product's category_id matches the current category's id, so the filter passes whenever the category has any product on file.
Inside the subquery, SELECT 1 is a placeholder. EXISTS cares only about whether any row comes back; it never inspects the projected value. Writing SELECT 1 makes the intent explicit.

Why this and not a join with DISTINCT

Joining categories to products and then applying DISTINCT produces the same set of categories, but the join first multiplies each category by every matching product before the deduplication collapses it back down. EXISTS short-circuits on the first match per category, which is both cleaner to read and the structural shape the problem is testing.

You practiced the existence-check pattern — keeping a parent only when at least one matching child is on record.

Return the `id` and `name` of every category that has at least one product assigned to it

The shape

Clause by clause

Why this and not a join with DISTINCT

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