Skip to content
Quantoros

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

Related questions