All Product and Category Combinations

The problem

Brightlane's merchandising team is building a product-category mapping tool and needs the complete grid of every product paired with every available category. The tool will use the grid to allow staff to reassign products by toggling cells.

Write a query to return the product name and category name for every possible combination.

Assumptions:

The products table contains every product in the catalogue.
The categories table contains every defined category.
The output should pair every product with every category, including the category each product is currently assigned to and every other one.

Output:

One row per product-category combination, with columns product_name and 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
  p.name AS product_name,
  c.name AS category_name
FROM
  products p
  CROSS JOIN categories c

The shape

CROSS JOIN pairs every product with every category, producing the complete grid the merchandising tool needs to render its toggle interface. Every cell has to exist before staff can switch between them.

Clause by clause

FROM products p CROSS JOIN categories c is the operation. No ON clause. Every row in products pairs with every row in categories. A 25-product catalog and 6 categories produces 150 rows — one for every cell in the toggle grid.
p.name AS product_name reads from the products side of each paired row. Both source tables have a name column, so the table alias p is what tells PostgreSQL which one to read.
c.name AS category_name does the same on the categories side, pulling the category label.

Why this and not `INNER JOIN ON p.category_id = c.id`

An INNER JOIN on the assignment column would return only the rows where each product is paired with its current category — one row per product, the existing mapping. The toggle UI needs every cell of the grid to be reachable, including reassignments that haven't happened yet. CROSS JOIN produces that full grid by leaving the relationship out of the join entirely.

You practiced CROSS JOIN for a UI-scaffolding use case. The complete grid is what a multi-select interface or pivot-style report needs as a starting point — the actual relationships are layered on top later, often via a LEFT JOIN against the relationship table.

Return the product name and category name for every possible combination

The shape

Clause by clause

Why this and not `INNER JOIN ON p.category_id = c.id`

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 INNER JOIN ON p.category_id = c.id

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

Why this and not `INNER JOIN ON p.category_id = c.id`