Brightlane's catalogue team is auditing category assignments. Starting from the full grid of every product paired with every category, the auditor wants to keep only the pairs where the product is actually assigned to that category.
Write a query that produces the cross-product of products and categories and then narrows it to the actual assignments.
Assumptions:
- The match condition is
products.category_id = categories.id. Applying this condition over the cross-product yields exactly the rows where each product is paired with its own category. - Products with an unresolved
category_idand categories with no products do not appear in the result.
Output:
- One row per actual assignment, 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,
c.name AS category_name
FROM
products p
CROSS JOIN categories c
WHERE
p.category_id = c.idThe shape
The CROSS JOIN produces every possible product-category pairing first; the WHERE then keeps only the pairs where the product's category_id matches the category's id. What survives is one row per product paired with its own category — the actual assignments. This is what an INNER JOIN does under the hood, written out longhand.
Clause by clause
FROM products p CROSS JOIN categories cis the full cartesian product. Ifproductshas 25 rows andcategorieshas 6, this intermediate result has 150 rows — every product paired with every category, regardless of relationship.WHERE p.category_id = c.idis the match condition, applied as a filter on the cross-product. For each of the 150 pairs, PostgreSQL checks whether the product's recorded category ID equals the category's ID. Most pairs fail this check and drop out. Only the ones where the product is paired with its own category survive — one row per product.p.name AS product_nameandc.name AS category_nameread from each surviving pair. The table aliases disambiguate the twonamecolumns.
Why this and not INNER JOIN
SELECT ... FROM products p INNER JOIN categories c ON p.category_id = c.id returns the same rows. PostgreSQL is free to execute the two queries identically — the planner is smart enough to push the equality into the join itself rather than materialise the full cross-product. The result set is the same; the runtime is the same.
The value of writing it this way is conceptual. INNER JOIN ... ON is shorthand for "cross-product, then keep the rows where the condition holds." Every inner join is a cross-product filtered by its ON condition. Seeing the longhand once makes the shorthand legible — and makes accidental cross-products easier to spot, because you can recognise the row-explosion failure mode for what it is.
The trap
The trap is the inverse: forgetting the WHERE condition. SELECT ... FROM products p CROSS JOIN categories c with no filter returns all 150 rows — every product paired with every category, including 144 pairings that are wrong. The query runs, no error fires, and the audit ships with five wrong rows per product. Anywhere a cross-product is intended to become an inner join via a filter, the filter is mandatory; without it, the row count is the product of the table sizes and the result is meaningless.
You practiced expressing an INNER JOIN as a CROSS JOIN plus a WHERE condition on the matching key. The cross-product framing exposes what the INNER JOIN shorthand is doing underneath.
