Zomato mid EXISTSNOT EXISTSCorrelated SubqueryLEFT JOINORDER BY

Find every restaurant that has never had a cancelled order — meaning it has zero orders with status = 'cancelled'. This includes restaurants with no orders at all, since they trivially satisfy 'never cancelled.' Use EXISTS or NOT EXISTS with a correlated subquery rather than aggregating and filtering afterward. Return restaurant_name and city, sorted alphabetically by restaurant_name.

Zomato · Order Reliability — practice this real-world SQL scenario live in your browser.

📖 Story Zomato · Order Reliability

Thursday morning at Zomato's Restaurant Quality team. Leadership wants a list of restaurant partners who have a spotless order-fulfillment record — every single order they've ever received actually got delivered, none cancelled. The orders table only has a row for an order that actually happened, so a restaurant that's brand new and hasn't received any orders yet simply has no rows in it at all — that's not missing data, it's a restaurant that's never had the chance to disappoint anyone. One order is also still sitting with a NULL status because the delivery hasn't been marked complete or cancelled yet; it shouldn't count against a restaurant either way until it resolves.

🎯 Your Mission

📋 Table Structure

🗂 restaurants

restaurant_id INTEGER 1

restaurant_name TEXT Spice Route

city TEXT Chennai

🗂 orders

order_id INTEGER 1

restaurant_id INTEGER 1

status TEXT delivered

order_amount REAL 450

⚡ Step-by-Step Walkthrough

Look at every restaurant next to its orders (or lack of them)

query.sql

SELECT r.restaurant_id, r.restaurant_name, o.order_id, o.status, o.order_amount
FROM restaurants r
LEFT JOIN orders o ON o.restaurant_id = r.restaurant_id
ORDER BY r.restaurant_id, o.order_id;

💡 Explanation

LEFT JOIN keeps every restaurant even if it has no matching orders — Tandoor Nights (restaurant_id 5) shows up with NULL for every order column, because it genuinely has zero rows in orders, not because a join went wrong.
Wok This Way has three order rows, and the third one (order_id 9) has a NULL status — that order hasn't been marked delivered or cancelled yet, it's simply still in progress.
Spice Route and Green Bowl each have exactly one cancelled order mixed in with delivered ones — the eventual query needs to catch a restaurant even if only one of several orders was cancelled.
Nothing is filtered yet — this step exists purely to see, restaurant by restaurant, what 'has an order' and 'has no orders at all' actually look like side by side.

Warm-up: find restaurants that DO have a cancelled order, using EXISTS

query.sql

SELECT restaurant_name, city
FROM restaurants r
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.restaurant_id = r.restaurant_id AND o.status = 'cancelled'
)
ORDER BY restaurant_name;

💡 Explanation

EXISTS doesn't return any actual data from the subquery — SELECT 1 is a convention, not a meaningful column; the outer query only cares whether the subquery finds at least one matching row, not what that row contains.
The subquery is correlated: o.restaurant_id = r.restaurant_id ties it back to whichever restaurant the outer query is currently checking, so it re-runs conceptually once per restaurant rather than running once for the whole table.
Wok This Way's NULL-status order (order_id 9) doesn't make it match here — o.status = 'cancelled' compares NULL to 'cancelled', which evaluates to UNKNOWN, not TRUE, so that row can't satisfy the EXISTS condition.
Only Spice Route and Green Bowl come back — the two restaurants whose orders table actually contains at least one row where status is exactly 'cancelled'.

Flip it: NOT EXISTS finds restaurants with no cancelled order

query.sql

SELECT restaurant_name, city
FROM restaurants r
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.restaurant_id = r.restaurant_id AND o.status = 'cancelled'
)
ORDER BY restaurant_name;

💡 Explanation

NOT EXISTS is exactly the previous step's condition, inverted — a restaurant qualifies the moment the correlated subquery finds zero matching rows instead of at least one.
Tandoor Nights qualifies immediately: its correlated subquery runs against zero orders rows for restaurant_id 5, finds nothing, and 'finds nothing' is precisely what NOT EXISTS is looking for — no special-casing for 'has no orders' was needed anywhere in the query.
Curry Leaf and Wok This Way qualify too — every order they do have is either delivered or NULL, and neither of those is 'cancelled', so their correlated subquery also comes back empty.
Three restaurants come back, and this result is a clean complement of step 2's two restaurants — together they account for all five restaurants, which is a useful sanity check whenever EXISTS and NOT EXISTS are used to split one table into two disjoint groups.

▶ Practice this live ← Browse all questions