Brightlane's inventory team is reviewing which catalogue items have never moved.
Write a query to return the product name for every product that has never appeared on any order line.
Assumptions:
- The
productstable contains every product in the catalogue. - The
order_itemstable contains one row per product per order;product_idlinks each line item back to a product. - A product that has never moved is one whose
iddoes not appear inorder_items.product_id.
Output:
- One row per never-sold product, with a single column
product_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
FROM
products p
LEFT JOIN order_items oi ON p.id = oi.product_id
WHERE
oi.order_id IS NULLThe shape
The LEFT JOIN from products to order_items keeps every product; the WHERE oi.order_id IS NULL filter then keeps only the products whose line-item columns came back as NULL — meaning the product never appeared on any order line. The 33 rows in the result are exactly the never-sold catalogue items.
Clause by clause
SELECT p.name AS product_namereturns just the product name from the left side — that's all the inventory team needs to identify the items.FROM products p LEFT JOIN order_items oi ON p.id = oi.product_idpairs each product with each of the order lines that reference it. Products that have sold appear once per line item with real values inoi.*. Products that have never sold appear once withNULLin everyoi.*column.WHERE oi.order_id IS NULLkeeps only the unmatched products.order_items.order_idis part of the junction table's identity — every real line item has both an order and a product. Sooi.order_id IS NULLis the unmatched signal: the join synthesised aNULLrow to preserve a product that has no line item anywhere.
Why this and not INNER JOIN
INNER JOIN would return only product-line pairs that exist — every product that has sold at least once. The never-sold products would silently disappear before any filter could pick them out. The inventory question is the opposite of what INNER JOIN returns, and the shape that surfaces the absence is LEFT JOIN plus IS NULL on the right side.
The trap
With a junction table on the right, the trap is checking NULL on the wrong column. WHERE oi.product_id IS NULL works on this query because every right-side column is NULL for unmatched rows. But oi.product_id is the column being joined on — if the schema allowed product_id to be NULL on a real row (a defective record, an orphan), the filter would conflate two different facts: "the row was synthesised by the outer join" and "the row exists but has a missing product reference." Checking a primary-key-style column (oi.order_id in this junction table, or a true primary key when there is one) keeps the unmatched-by-outer-join signal clean.
You practiced the anti-join against a junction table. The same LEFT JOIN ... WHERE right.key IS NULL shape applies — the only thing that changes is which side of the join you anchor on (the dimension you want to enumerate, in this case products).
