Menu Items Nobody Has Ordered

Easy
150.

Menu Items Nobody Has Ordered

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

ColumnType
restaurant_idint
namevarchar

Primary key: restaurant_id.

One row per restaurant.

Table: MenuItem

ColumnType
item_idint
restaurant_idint
item_namevarchar
priceint

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

ColumnType
order_idint
item_idint
quantityint

Primary key (together): order_id, item_id.

One row per dish in an order; item_id is always in MenuItem.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Restaurant

restaurant_idname
1Biryani Bay
2Dosa Junction

MenuItem

item_idrestaurant_iditem_nameprice
101Chicken Biryani280
111Gulab Jamun70
121Mutton Rogan Josh380
202Masala Dosa90
212Gulab Jamun60
222Idli Sambar70

OrderLine

order_iditem_idquantity
9001102
9001111
9002201
9003101
9003223

Output

restaurantitem_nameprice
Biryani BayMutton Rogan Josh380
Dosa JunctionGulab Jamun60
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
restaurant_idint
namevarchar

Primary key: restaurant_id.

One row per restaurant.

Table: MenuItem

ColumnType
item_idint
restaurant_idint
item_namevarchar
priceint

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

ColumnType
order_idint
item_idint
quantityint

Primary key: order_id, item_id.

One row per dish in an order; item_id is always in MenuItem.

Examples

Example 1

Restaurant

restaurant_idname
1Biryani Bay
2Dosa Junction

MenuItem

item_idrestaurant_iditem_nameprice
101Chicken Biryani280
111Gulab Jamun70
121Mutton Rogan Josh380
202Masala Dosa90
212Gulab Jamun60
222Idli Sambar70

OrderLine

order_iditem_idquantity
9001102
9001111
9002201
9003101
9003223

Output

restaurantitem_nameprice
Biryani BayMutton Rogan Josh380
Dosa JunctionGulab Jamun60

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 NULL

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 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 →