Brightlane's product team is reviewing catalogue organisation. They want a report listing each product alongside its category — the gap between the report's row count and the catalogue's true product count is itself the audit finding.
Write a query that returns the product name and category name for every product whose category_id matches a row in categories.
Assumptions:
- The
productstable contains 63 products in the catalogue. - The
categoriestable contains every defined category, identified bycategories.id. - Some products have a
category_idvalue that does not match any row incategories, or have a missingcategory_id. Those products will not appear in the result. - The result will contain fewer than 63 rows; the missing products are the ones the audit is meant to surface.
Output:
- One row per product whose category resolves, 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
JOIN categories cat ON p.category_id = cat.idThe shape
INNER JOIN's drop-unmatched behavior is the audit. The catalogue has 63 products, but only the ones whose category_id matches a row in categories come through the join. Every product with a missing or invalid category_id is silently excluded. The size of the gap between 63 and the result row count is the data-quality finding the product team came to read.
Clause by clause
FROM products preads from the catalogue side: all 63 products, regardless of category assignment.JOIN categories cat ON p.category_id = cat.idpairs each product with its category. For products whosecategory_idlines up with acategories.id, the row passes through with the category's columns attached. For products with aNULLcategory_id, or with acategory_idvalue that no row incategoriesmatches, the join drops the row from the result. There is no error, no warning, no flag.SELECT p.name AS product_name, cat.name AS category_namereturns the product's name and the resolved category's name. Bothnamecolumns are qualified because both tables carry one.
Why this and not a different shape
The audit could be written several ways, but INNER JOIN is the right tool here precisely because the prompt frames the gap as the finding. The product team can compare the row count to the known catalogue total (63) and read the difference directly as "how many products fail to map." The detail of which products fall out belongs to a later report; this one is the count-the-gap version.
The trap
INNER JOIN's drop behavior is silent. A learner running this query for the first time might see, say, 60 rows come back and assume the catalogue has 60 products. The query gives no signal that 3 were dropped. The result row count is the only evidence, and only if you already know the source row count to compare against.
The rule: any time an INNER JOIN row count comes in lower than the left-side source's row count, the gap is the number of rows that failed to match. When that gap matters for an audit, a coverage check, or a data-quality review, verify it explicitly rather than assuming the result row count equals the source row count.
You practiced relying on INNER JOIN's drop-unmatched behavior as the answer to a real business question. Useful when the absence of a row is itself the finding.
