Brightlane's product management team needs a complete reconciliation between the product catalogue and the category list:
- Every product must appear in the output, even if it has no assigned category (category name will be missing).
- Every category must also appear, even if no products belong to it (product name will be missing).
Write a query to return the product name and category name for every row in the reconciliation.
Assumptions:
- The
productstable contains every product in the catalogue. - The
categoriestable contains every defined category. - The result combines three groups of rows: matched product-category pairs, products with no resolving category, and categories with no products. All three must appear in a single result set.
Output:
- One row per matched pair, plus one row per orphan product, plus one row per orphan category, with columns
product_nameandcategory_name.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
p.name AS product_name,
cat.name AS category_name
FROM
products p
FULL OUTER JOIN categories cat ON p.category_id = cat.idThe shape
Three categories of rows in a single result — matched product-category pairs, orphan products with category_name as NULL, and orphan categories with product_name as NULL. FULL OUTER JOIN is the only single-statement join that produces all three. LEFT JOIN would drop the orphan categories; RIGHT JOIN would drop the orphan products; INNER JOIN would drop both.
Clause by clause
SELECT p.name AS product_name, cat.name AS category_namepulls one column from each side. On a matched row both values are real. On an orphan-product row,category_nameisNULLbecause the categories side was padded in by the outer join. On an orphan-category row,product_nameisNULLfor the same reason on the opposite side. Reading thoseNULLpatterns is how product management tells the three groups apart.FROM products p FULL OUTER JOIN categories cat ON p.category_id = cat.idis the reconciliation. TheONcondition pairs a product with its category whenevercategory_idresolves. Where it does, the row is assembled from both sides. Where it doesn't on the products side (the product carries acategory_idthat points nowhere), the row is still kept with the categories columnsNULL. Where it doesn't on the categories side (no product carries this category'sid), the row is also kept with the products columnsNULL. TheFULLpart of the keyword makes that guarantee in both directions; without it, one side or the other would be silently dropped.- No
WHERE. The team asked for the combined view, so every row the join produces belongs in the output.
Why this and not a LEFT JOIN
LEFT JOIN products → categories gives the matched rows and the orphan products, but loses the orphan categories. Flipping the table order (or using RIGHT JOIN) gives the matched rows and the orphan categories, but loses the orphan products. Neither single-direction join covers both. FULL OUTER JOIN is the one statement that does — matched rows appear once and both flavors of orphan come along.
The trap
The trap is reading the prompt as two separate questions — "products with no category" and "categories with no products" — and reaching for a single-direction join for each. Whichever direction you pick silently drops one of the two orphan categories the team asked to see. The moment both gaps need to appear in the same result, only FULL OUTER JOIN answers the question.
You practiced FULL OUTER JOIN as the only single-statement way to surface orphans on both sides. The recurring rule: LEFT JOIN keeps left orphans, RIGHT JOIN keeps right orphans, FULL OUTER JOIN keeps both — and there's no shorter way to get both kinds of gaps in one query.
