Customers Who Never Order
Problem
Write a SQL query to return the names of customers from Customers who never placed any order in Orders.
SQL: SELECT name AS Customers FROM Customers WHERE id NOT IN (SELECT customerId FROM Orders);
Customers = [(1,Joe),(2,Henry),(3,Sam),(4,Max)]; Orders = [(101,3),(102,1)]["Henry","Max"]SELECT name AS Customers FROM Customers
WHERE id NOT IN (SELECT customerId FROM Orders);Explanation
We want the customers who never placed an order. The straightforward way is: gather every customer id that did order, then keep only the customers whose id is not in that list.
The inner query SELECT customerId FROM Orders produces the set of ids that appear in the Orders table. The outer query then filters customers with WHERE id NOT IN (...), which is an anti-join — it keeps rows that have no match on the other side.
Under the hood the database typically loads those order ids into a hash set, so checking whether each customer's id is present is essentially instant.
Example: Customers are Joe(1), Henry(2), Sam(3), Max(4) and Orders reference customers 3 and 1. The ordering set is {1, 3}. Henry(2) and Max(4) are not in it, so the result is ["Henry", "Max"].
Reading both tables once and doing constant-time set lookups makes this a single efficient sweep.