Tables customers(id int primary key, name text, city text) and orders(id int primary key, customer_id int references customers(id), amount int, ordered_on date).
Marketing wants to email customers who have never ordered. Return their id and name, ordered by id.
Constraints
- Write one SELECT statement for PostgreSQL 16.
- Output columns and row order must match exactly.
Input and output
- Input
- The setup script in the test case creates and fills the tables described above.
- Output
- Columns: id, name.
Example
Input
create table customers (id int primary key, name text not null, city text not null);
create table orders (id int primary key, customer_id int not null references customers(id), amount int not null, ordered_on date not null);
insert into customers values (1,'Riya Das','Pune'),(2,'Dev Rao','Delhi'),(3,'Sana Khan','Kochi');
insert into orders values (10,1,250,'2025-03-02'),(11,1,90,'2025-03-09');Output
2|Dev Rao
3|Sana KhanOnly Riya has orders, so Dev and Sana are returned.
Show hints
- A LEFT JOIN keeps every customer; customers with no orders get NULLs on the order side.
- Filter on a column of orders that can never be NULL in a real match.
Show the explanation
customers c LEFT JOIN orders o ON o.customer_id = c.id keeps every customer; for customers without orders, every o column is NULL. WHERE o.id IS NULL keeps exactly those. NOT EXISTS (select 1 from orders where customer_id = c.id) is an equally good answer. Avoid NOT IN (select customer_id ...) when that column can contain NULLs: a single NULL makes NOT IN return no rows.
Original UniCircle question. Last reviewed 22 Oct 2026.