Subqueries
Use the result of one query inside another — in WHERE, and as a value.
- Filter with IN against a subquery
- Use a scalar subquery as a single value
- Recognise when a join is clearer than a subquery
A subquery is a query nested inside another. It runs first, and its result
feeds the outer query. The most common use is filtering with IN.
Filtering with IN (subquery)
"Customers who have placed at least one order" — the inner query lists the
customer ids that appear in orders; the outer keeps customers in that set:
Flip it with NOT IN to find customers who have never ordered.
Scalar subqueries
A subquery that returns a single value can be used like one — e.g. compare each product to the average price:
The inner (SELECT AVG(price) …) evaluates to one number; the outer query
compares each product against it.
Subquery or join?
Many IN-subqueries can also be written as joins, and vice versa. Rough guidance:
- Reach for a subquery when you're asking a yes/no membership question ("is this id in that set?") — it often reads more directly.
- Reach for a join when you need columns from both tables in the output.
Both are correct; pick whichever makes the intent clearest.
Where to go next
Subqueries nest inline. Next: CTEs — naming a subquery up front so complex queries read top to bottom.