Customers Who Never Ordered
Problem
Two tables: customer(id, name) and orders(id, customer_id), where orders.customer_id points at customer.id. Guest checkouts are stored with a NULL customer_id. Write a query that returns the names of the customers who have never placed an order, as a single column name, sorted by name.
Examples
Input: customer = [(1, "Ana"), (2, "Ben"), (3, "Cy")]
orders = [(10, 1), (11, 1), (12, 3)]
Output: [("Ben",)]
Why: Ana has two orders and Cy has oneInput: customer = [(1, "Ana"), (2, "Ben")]
orders = [(10, 1), (11, None)]
Output: [("Ben",)]
Why: order 11 was a guest checkout and belongs to nobodyInput: customer = [(1, "Ana"), (2, "Ben")]
orders = []
Output: [("Ana",), ("Ben",)]
Why: edge case, with no orders at all every customer qualifiesHints
0 / 3
An inner join only keeps customers that have a matching order, which is the opposite of what is asked. Which join keeps a customer even when nothing matches?
A LEFT JOIN keeps every customer and fills the order columns with NULL when there is no match. Those NULLs are the signal.
LEFT JOIN orders ON orders.customer_id = customer.id, then keep the rows WHERE orders.id IS NULL. Be careful with NOT IN over a column that holds NULL: it returns no rows at all.
Solution
A LEFT JOIN keeps every customer and pairs each one with its orders; a customer with no order is paired with a single row of NULLs. Testing a column that can never be NULL in a real order, such as o.id, picks out exactly those customers. This shape is called an anti-join, and NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) expresses the same thing. The tempting WHERE id NOT IN (SELECT customer_id FROM orders) is wrong here: the guest order puts a NULL in that list, x NOT IN (..., NULL) is never true, and the query returns nothing. With an index on orders.customer_id each customer costs one index lookup, O(n log m) for n customers and m orders.
import sqlite3
QUERY = """
SELECT c.name
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL
ORDER BY c.name
"""
def run(customers, orders):
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE customer (id INTEGER PRIMARY KEY, name TEXT)")
db.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER)")
db.executemany("INSERT INTO customer VALUES (?, ?)", customers)
db.executemany("INSERT INTO orders VALUES (?, ?)", orders)
return db.execute(QUERY).fetchall()
print(run([(1, "Ana"), (2, "Ben"), (3, "Cy")], [(10, 1), (11, 1), (12, 3)])) # -> [('Ben',)]
print(run([(1, "Ana"), (2, "Ben")], [(10, 1), (11, None)])) # -> [('Ben',)]
print(run([(1, "Ana"), (2, "Ben")], [])) # -> [('Ana',), ('Ben',)]Stuck on the idea rather than the code? INNER and OUTER JOINs covers it.