Subqueries with IN
Matching against a list.
Remember in? Where city in, Dublin, London, Sydney. It checks a value against a list. Well, that list doesn't have to be typed by hand. A subquery can build it for you.

- IN checks a list
- You typed the list by hand
- A subquery can build it
IN with a subquery
Helen wants to call customers who have returned an order, to ask what went wrong. The subquery lists the customer IDs from returned orders. The outer query keeps customers whose ID is in that list. Run it. Here are the first five customers to call.
SELECT first_name, last_name, city
FROM customers
WHERE customer_id IN (SELECT customer_id
FROM orders
WHERE status = 'returned')
LIMIT 5;โโโโโโโโโโโโโโฌโโโโโโโโโโโโฌโโโโโโโโโโโโ โ first_name โ last_name โ city โ โโโโโโโโโโโโโโผโโโโโโโโโโโโผโโโโโโโโโโโโค โ Sam โ Costa โ Dublin โ โ Diego โ Ali โ Vancouver โ โ Carmen โ Ahmed โ Melbourne โ โ Vera โ Smith โ Vancouver โ โ Ava โ Brown โ Toronto โ โโโโโโโโโโโโโโดโโโโโโโโโโโโดโโโโโโโโโโโโ
How many to call?
Same idea, but counted. Run it. Fifty eight customers have returned at least one order. Notice, sixty three orders were returned. But some customers returned more than once, and in only counts each customer one time.
SELECT COUNT(*) AS customers_who_returned
FROM customers
WHERE customer_id IN (SELECT customer_id
FROM orders
WHERE status = 'returned');โโโโโโโโโโโโโโโโโโโโโโโโโโ โ customers_who_returned โ โโโโโโโโโโโโโโโโโโโโโโโโโโค โ 58 โ โโโโโโโโโโโโโโโโโโโโโโโโโโ
IN versus JOIN
You could answer this with a join too. So which should you use? A join gives you columns from both tables. In just filters, and never creates duplicate rows. If you only need to filter, in is often simpler, and avoids the double counting trap.
JOIN
- Columns from both tables
- Can duplicate rows
- Use to show data
IN (subquery)
- Columns from one table
- Never duplicates
- Use to filter
Summary
In, with a subquery, filters using a list that SQL builds for you. Next, subqueries in even more places.
Key takeaways- IN (SELECT ...)
- The list builds itself
- No duplicates