Level 7 · Sign-ups who never bought

An anti-join keeps the rows of one table that have no match in another. NOT EXISTS is the most readable and the safest way to write it.

Recap: three anti-joins

-- 1. NOT EXISTS
WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id)

-- 2. LEFT JOIN ... IS NULL
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL

-- 3. NOT IN
WHERE c.customer_id NOT IN (SELECT customer_id FROM orders)

All three answer “customers without orders” here. NOT EXISTS stops at the first match it finds and never duplicates rows. The LEFT JOIN version needs any extra condition on orders in its ON clause. NOT IN breaks if the subquery can return a NULL: x NOT IN (1, NULL) is never true, so the query returns nothing. orders.customer_id is never NULL, but many real columns are.

The subquery is correlated: o.customer_id = c.customer_id ties it to the customer on the current outer row, so it is evaluated per customer rather than once.

The SELECT 1 inside EXISTS is a convention. EXISTS only checks whether a row exists, so the selected value is never used.

Where the condition goes

The ticket says cancelled orders do not count as buying. That condition belongs inside the subquery: “there is no order that is completed”. Of the 297 customers who signed up from January to June 2025, 41 have no completed order. Four of those did place an order that was cancelled or is still pending, so a plain “no orders at all” check would have returned only 37 and missed them.

Four of the 41 ids are above 2000. Those are the deliberate duplicate customer records in this dataset (same person, second sign-up), which never have orders. On the job, deduplicate before sending a campaign, or the same person gets two nudges.

Common mistakes

• Putting status = 'completed' in the outer WHERE. The outer query has no order columns; with a join, the filter would remove the very customers you want.
• Using NOT IN on a column that can be NULL.

In SQL Server

NOT EXISTS is written the same way and is the recommended anti-join there too. Date literals such as '2025-07-01' compare correctly with a date column.