Menu Items Nobody Has Ordered — SQL Joins Problem
The menu team wants to prune dead dishes. An order line records one dish in one order; a dish that appears in no order line has never been ordered.
- Difficulty: Easy
- Topics: Joins, Subqueries
- Dialect: MySQL
- Problem: #150
Problem statement
The menu team wants to prune dead dishes. An order line records one dish in one order; a dish that appears in no order line has never been ordered.
Return every menu item with no order line at all, as columns restaurant (the restaurant's name), item_name and price. Return the rows in any order.
Tables
Table: Restaurant
| Column | Type |
|---|---|
| restaurant_id | int |
| name | varchar |
Primary key: restaurant_id.
One row per restaurant.
Table: MenuItem
| Column | Type |
|---|---|
| item_id | int |
| restaurant_id | int |
| item_name | varchar |
| price | int |
Primary key: item_id.
One row per dish on a restaurant's menu; restaurant_id is always in Restaurant. Two restaurants may sell a dish of the same name.
Table: OrderLine
| Column | Type |
|---|---|
| order_id | int |
| item_id | int |
| quantity | int |
Primary key: order_id, item_id.
One row per dish in an order; item_id is always in MenuItem.
Examples
Example 1
Restaurant
| restaurant_id | name |
|---|---|
| 1 | Biryani Bay |
| 2 | Dosa Junction |
MenuItem
| item_id | restaurant_id | item_name | price |
|---|---|---|---|
| 10 | 1 | Chicken Biryani | 280 |
| 11 | 1 | Gulab Jamun | 70 |
| 12 | 1 | Mutton Rogan Josh | 380 |
| 20 | 2 | Masala Dosa | 90 |
| 21 | 2 | Gulab Jamun | 60 |
| 22 | 2 | Idli Sambar | 70 |
OrderLine
| order_id | item_id | quantity |
|---|---|---|
| 9001 | 10 | 2 |
| 9001 | 11 | 1 |
| 9002 | 20 | 1 |
| 9003 | 10 | 1 |
| 9003 | 22 | 3 |
Output
| restaurant | item_name | price |
|---|---|---|
| Biryani Bay | Mutton Rogan Josh | 380 |
| Dosa Junction | Gulab Jamun | 60 |
How to solve Menu Items Nobody Has Ordered
This is an anti join: menu items that have no partner in OrderLine. Start from MenuItem, join Restaurant for the name (every item has a restaurant, so an inner join loses nothing), and LEFT JOIN OrderLine on item_id. An item that was ordered appears once per order line; an item that never was appears once with NULL in every order-line column, and o.order_id IS NULL keeps exactly those.
Match on item_id, not on item_name: two restaurants both sell Gulab Jamun, and one restaurant's sales say nothing about the other's dish.
NOT EXISTS states the same rule directly and stops at the first matching line. NOT IN is safe here only because OrderLine.item_id is part of the primary key and never NULL — with a nullable column it would silently return nothing. With an index on OrderLine.item_id every form is one lookup per menu item.
Reference solution (MySQL)
SELECT r.name AS restaurant, m.item_name, m.price
FROM MenuItem m
JOIN Restaurant r ON r.restaurant_id = m.restaurant_id
LEFT JOIN OrderLine o ON o.item_id = m.item_id
WHERE o.order_id IS NULLAnother way
SELECT r.name AS restaurant, m.item_name, m.price FROM MenuItem m JOIN Restaurant r ON r.restaurant_id = m.restaurant_id WHERE NOT EXISTS (SELECT 1 FROM OrderLine o WHERE o.item_id = m.item_id)Another way
SELECT r.name AS restaurant, m.item_name, m.price FROM MenuItem m JOIN Restaurant r ON r.restaurant_id = m.restaurant_id WHERE m.item_id NOT IN (SELECT item_id FROM OrderLine)← Payable Amount on Surge-Priced Orders · Delivered GMV of Every Dark Store →