Customers who never placed an order
Return customer ID and name for customers with no order. Orders with NULL customer_id must not suppress the result. Return only the requested columns and follow the specified ordering. This question probes not exists implements a null-safe anti-join, unlike not in over nullable foreign keys.
Input
Setup script defining customers(id INT, name VARCHAR(30)); orders(id INT, customer_id INT, amount INT, order_date VARCHAR(10)).
Output
id, name; ascending customer ID.
Constraints
0–10000 rows per table. IDs are unique and non-NULL unless stated otherwise; foreign keys may be NULL. Numeric amounts are integers from 0 to 1000000. Dates are valid YYYY-MM-DD text. Text uses case-sensitive comparison; test data has consistent case. Duplicate business values and NULLs are allowed unless excluded by the task.
Example
Tables and sample rows
CREATE TABLE customers(id INT, name VARCHAR(30)); INSERT INTO customers VALUES (1,'Ana'),(2,'Ben'),(3,'Cara'); CREATE TABLE orders(id INT, customer_id INT, amount INT, order_date VARCHAR(10)); INSERT INTO orders VALUES (1,1,100,'2024-01-01'),(2,1,200,'2024-02-01'),(3,2,100,'2025-01-01'),(4,NULL,50,'2024-12-31');
Expected result
id | name 3 | Cara
Another dataset (1)
CREATE TABLE customers(id INT, name VARCHAR(30)); INSERT INTO customers VALUES (1,'Solo'),(2,'None'); CREATE TABLE orders(id INT, customer_id INT, amount INT, order_date VARCHAR(10)); INSERT INTO orders VALUES (1,1,0,'2023-12-31'),(2,NULL,10,'2024-01-01');
Expected result
id | name 2 | None
Covers missing matches, ties, or NULL values without relying on insertion order.
Hints (2)
- Correlate each customer with matching orders.
- Use NOT EXISTS to avoid the NOT IN NULL trap.
Target complexity: time O(c * o) · space O(1)