Products with No Matching Category — FULL OUTER JOIN in SQL

The problem

Brightlane's catalogue team is cross-checking products against the category table.

Write a query to return the product name for every product whose category_id does not resolve to any row in categories.

Assumptions:

A product with no resolving category has a missing category_id or a value that doesn't appear as categories.id.
The result returns one row per such orphan product.

Output:

One row per orphan product, with a single column product_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
  p.name AS product_name
FROM
  products p
  FULL OUTER JOIN categories cat ON p.category_id = cat.id
WHERE
  cat.id IS NULL

The shape

The outer join produces three categories of rows: matched products, orphan products with the categories side NULL, and orphan categories with the products side NULL. WHERE cat.id IS NULL keeps only the orphan-product rows — products whose category_id didn't resolve to any row in categories. For Brightlane that's three rows: Gift Card $50, Gift Card $100, and Mystery Bundle.

Clause by clause

SELECT p.name AS product_name returns just the product name. Everything else the join carried is scaffolding for the filter; only this column makes it into the output.
FROM products p FULL OUTER JOIN categories cat ON p.category_id = cat.id is the reconciliation. For matched products cat.id resolves to a real value; for orphan products the join still keeps the row and fills the categories side with NULL; for orphan categories the join keeps the row and fills the products side with NULL.
WHERE cat.id IS NULL is the anti-join filter. It runs after the join produces its row set and keeps only the rows where the categories side came back empty. Matched rows have a real cat.id, so they drop out. Orphan-category rows have a real cat.id too (and a NULL product name), so they drop out — which means the product_name column on every surviving row is real and not NULL.

Why this and not a `LEFT JOIN`

A LEFT JOIN between the same two tables, anchored on products, would return exactly the same three rows after the same WHERE cat.id IS NULL filter. For a one-sided anti-join, LEFT JOIN is the more natural tool; it preserves the products side and discards anything else the join could have produced. The FULL OUTER JOIN form is doing extra work — keeping orphan categories the filter is about to throw away — but for this single-side question, both shapes land at the same answer.

You practiced isolating one side's orphans from a FULL OUTER JOIN (or equivalent LEFT JOIN). The recurring shape: after the outer join, WHERE right.key IS NULL keeps only the rows where the right side had no match.

Return the product name for every product whose `category_id` does not resolve to any row in `categories`

The shape

Clause by clause

Why this and not a `LEFT 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 a LEFT JOIN

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

Why this and not a `LEFT JOIN`