TalentdBuy now · ₹349
Chapter 2 · Q.115
The anti-join that quietly returns nothing
Swiggy's partner team wants the restaurants that have never had a delivered order, so they can call the owners. A restaurant with only cancelled or pending orders, or with no orders at all, belongs on the list.
Why this trips people
Moving the status filter into WHERE: WHERE o.delivery_status = 'delivered' AND o.order_id IS NULL can never be true, so the query returns nothing. Dropping the status filter altogether is the other wrong version: it finds Chai Point (no orders) but misses Paranthe Wali Gali, whose single cancelled order counts as a match.
See the answer
SELECT r.restaurant_id, r.restaurant_name, r.city
FROM swiggy_restaurants r
LEFT JOIN swiggy_orders o
ON o.restaurant_id = r.restaurant_id
AND o.delivery_status = 'delivered'
WHERE o.order_id IS NULL
ORDER BY r.restaurant_id;| restaurant_id | restaurant_name | city |
|---|---|---|
| 4 | Paranthe Wali Gali | Delhi |
| 5 | Chai Point | Hyderabad |
Key insight. In an anti-join, every condition that defines "a matching row" goes in the ON clause, and WHERE only checks that no match was found (a right-hand key IS NULL).
