Customers who never ordered

Marketing wants to email everyone who signed up but never bought anything.

A customer has ordered if there is at least one row in orders with their customer_id, whatever its status. A cancelled order still counts as an order: that person tried to buy.

Return one row per customer with no orders:

column	type	meaning
customer_id	integer	the customer
name	text	their name
country	text	their country code

Row order doesn’t matter.

Tables: customers, orders.

Approach

Keep each customer for whom no order exists.

SELECT c.customer_id, c.name, c.country
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

Why it works

NOT EXISTS asks a yes/no question per customer: is there any matching order? It never multiplies rows, so a customer with ten orders can’t appear ten times, and a customer with none appears once.

The LEFT JOIN ... WHERE o.order_id IS NULL version gives the same answer here. Be careful with NOT IN (SELECT customer_id FROM orders): if that subquery ever returns a NULL, NOT IN returns no rows at all. orders.customer_id is NOT NULL in this schema, so it works today, but NOT EXISTS is safe whatever the schema says.

Under the hood

DuckDB plans NOT EXISTS as an anti-join. It builds a hash table from orders.customer_id (only the key, about 176 distinct values) and streams the 200 customers past it. A customer is emitted when its probe finds nothing. That’s one build and one probe pass, not a subquery run once per customer.