Subquery with IN
- SQL course
- Intermediate
- Lesson 49/115
- 4 min
Use a subquery inside IN.
Subquery with IN
With IN a subquery returns a list. The outer query searches for values in that list.
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders);This query returns customers who placed orders.
name is selected from customers. WHERE keeps only the rows where id IN (SELECT customer_id FROM orders) holds.
Subquery with IN — details
An IN subquery can return many rows. Each value is looked up in the outer query.
For example, WHERE id IN (SELECT customer_id FROM orders) returns customers with at least one order.
Practice Subquery with IN
Find customers who ordered using IN (SELECT customer_id FROM orders).
Starter code
-- ordered customers
SELECT name FROM customers
WHERE idSample solution
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders);Expected output
name Ayşe Mehmet
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Subquery with IN: match the terms
| Term | Meaning |
|---|---|
IN |
list |
= |
single value |
ANY |
any |
ALL |
all |
2 Add IN
SELECT name FROM customers
WHERE id ____ (SELECT customer_id FROM orders);SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders);Explanation: IN returns customers whose id appears in the list returned by the subquery.
3 What does an IN subquery return?
- Nothing
- A table
- A single value
- A list Correct answer
Explanation: An IN subquery returns a list.