Has this customer ever ordered?
Marketing wants to run a small “thanks for being a customer” campaign — but only to people who’ve actually bought something. Signing up isn’t enough; they want customers who’ve placed a real order.
Find customers who have placed at least one order.
Alright. What does “has ordered” actually mean, in terms of the tables you've got?
Easy, I already know this one — JOIN customers to orders and read off the names.
SELECT c.name, c.country
FROM customers c
JOIN orders o ON o.customer_id = c.id;Running…
Run it and count: 22 rows. NOVA only has 10 customers who’ve ever ordered. Scroll to the top of that result and you’ll find Alice Sharma four times in a row — once for each order she’s placed:
| name | country |
|---|---|
| Alice Sharma | India |
| Alice Sharma | India |
| Alice Sharma | India |
| Alice Sharma | India |
This is the row-multiplication problem from a few lessons back: joining one customer row to many matching order rows produces one output row per match, not per customer. The query isn’t wrong — every name in there really has ordered — it just answers a different question than the one being asked.
You never actually cared how many orders each customer placed. You only wanted to know if at least one exists.
So... DISTINCT?
SELECT DISTINCT c.name, c.country
FROM customers c
JOIN orders o ON o.customer_id = c.id;| name | country |
|---|---|
| Alice Sharma | India |
| Bob Turner | USA |
| Diego Fernandez | Brazil |
| Emma Wilson | UK |
| Farah Khan | India |
| George Lee | USA |
| Ivan Petrov | Germany |
| Julia Souza | Brazil |
| Kevin Zhang | USA |
| Liam O'Connor | UK |
That’s the right 10 customers. But think about what the database actually did to get there: it built all 22 matched rows, then threw most of them away as duplicates. DISTINCT is cleaning up a mess the query didn’t need to make. There’s a tool that asks the real question — “does at least one order exist for this customer?” — directly.
That tool is EXISTS. It wraps a subquery and reduces it to a single yes/no: did that subquery find at least one row? It never collects any of the matches, so there’s nothing to deduplicate afterward.
Does at least one matching row exist? Just yes or no.
SELECT name, country
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
);Notice the inner query mentions c.id — a column from the outer query. That makes this a correlated subquery: it can’t run on its own, because it needs a specific customer row from the outside to check against. Conceptually, SQL asks the inner question once per outer row: “for this customer, does any order with a matching customer_id exist?” The SELECT 1 is just a convention — EXISTS never looks at what columns the subquery returns, only whether it returns anything at all.
Running…
Same 10 customers as the DISTINCT version — but this time nothing was ever duplicated in the first place. Alice shows up once, because the question was only ever asked about her once.
Try it: What about the customers who've never ordered at all?
Run your query to see results here.
Two rows: Chloe Martin and Hana Sato — the only two customers NOVA has never sold anything to. NOT EXISTS flips the same question around: “prove that nothing matches.” It’s the same customers you’d find with a LEFT JOIN and a check for NULL — just asked directly, instead of by joining everything and looking for the gaps afterward.
One more, using just the employees table: find every employee who manages at least one other employee. Remember, manager_id points back to another row’s id in the same table — a manager doesn’t have a special flag, they’re just someone that other rows point to.
Hint 1
For each employee, you need to know whether any other row in the same table has a manager_id pointing back at them.
Hint 2
This is the same EXISTS shape as the orders example, just self-referencing — the subquery looks at employees again, using a different alias, and compares its manager_id to the outer row's id.
Hint 3
SELECT name, department FROM employees e WHERE EXISTS (SELECT 1 FROM employees sub WHERE sub.manager_id = e.id);
Solution
Running…
Five people manage someone at NOVA — one per department, which makes sense once you notice they’re exactly the employees with no manager of their own. JOIN answers “show me the matches”; EXISTS answers “is there a match at all?” They can end up pointing at the same rows, but only one of them makes you clean up duplicates afterward.
EXISTS asks a yes/no question per row without ever collecting the matches. Reach for it (or NOT EXISTS) whenever the real question is “does at least one related row exist”, not “show me the matching rows.”