Return productname of products with no rows in orderitems, using the LEFT JOIN pattern. Table definitions (from the…
SQLLevel: IntermediateAnti-joins and missing data
Return product_name of products with no rows in order_items, using the LEFT JOIN pattern.
Table definitions (from the source exercise):
CREATE TABLE products(product_id INTEGER PRIMARY KEY, product_name TEXT, category TEXT, price REAL);
CREATE TABLE order_items(item_id INTEGER PRIMARY KEY, order_id INTEGER, product_id INTEGER, quantity INTEGER);
Hints
- Write the join keys and relationship cardinality.
- What happens to duplicates and unmatched rows? Check WHERE versus ON.
Answer
Approach: LEFT JOIN order_items; WHERE item is NULL.
SELECT p.product_name
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
WHERE oi.item_id IS NULL;
The source uses SQLite with a MySQL compatibility layer. Date and string functions are dialect dependent; the query is preserved as published.
Source and licence: SQL Practice Lab — Ebrahim Sharifi · MIT