Reconcile Products and Categories — FULL OUTER JOIN in SQL

The problem

Brightlane's catalogue team needs a complete reconciliation of the product and category tables — both directions:

Every product appears, including products whose category does not resolve (category name will be NULL).
Every category appears, including categories with no products assigned (product name will be NULL).

Write a query to return the product name and category name for every row in the combined view.

Assumptions:

The products table contains every product in the catalogue.
The categories table contains every defined category.
Some products have a category_id that does not resolve to any category; some categories have no products assigned.

Output:

One row per matched product-category pair, plus one row per unmatched product (with category_name as NULL), plus one row per unmatched category (with product_name as NULL).

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,
  cat.name AS category_name
FROM
  products p
  FULL OUTER JOIN categories cat ON p.category_id = cat.id

The shape

A FULL OUTER JOIN between products and categories keeps every row from both sides at once. Matched product-category pairs come out assembled; an orphan product comes out with category_name as NULL; an orphan category comes out with product_name as NULL. Three kinds of rows, one query.

Clause by clause

SELECT p.name AS product_name, cat.name AS category_name picks one column from each side of the join. On matched rows both values are real; on orphan rows the missing side's column is NULL, which is the signal the catalogue team reads as a gap.
FROM products p FULL OUTER JOIN categories cat ON p.category_id = cat.id is the reconciliation. The ON condition pairs a product with its category whenever category_id resolves; when it doesn't, the outer join keeps the row anyway and pads the other side with NULL. Because FULL makes that guarantee in both directions, a category with no products is preserved too.
No WHERE, no filter. The catalogue team asked for the combined view — matched plus both flavors of orphan — so every row the join produces belongs in the output.

You practiced a FULL OUTER JOIN to preserve every row from both tables. The recurring shape: when neither table has a privileged role and the question is "what's on each side, including the gaps," FULL OUTER JOIN is the only join type that surfaces both kinds of unmatched rows in a single result.

Return the product name and category name for every row in the combined view

The shape

Clause by clause

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