A subquery is a SELECT in brackets inside another query. The inner query runs first and hands its result to the outer one. Three shapes cover most uses:
- A single value, used like a number:
WHERE price > (SELECT AVG(price) FROM products). This is how you compare rows with an aggregate, which WHERE can't do directly.
- A list, used with
IN: WHERE customer_id IN (SELECT customer_id FROM orders WHERE status = 'returned').
- A table, used in
FROM: SELECT ... FROM (SELECT ...) AS t. It needs an alias.
A correlated subquery refers to the outer row, as in WHERE p.price > (SELECT AVG(p2.price) FROM products AS p2 WHERE p2.category = p.category), which compares each product with its own category's average. It runs once per outer row, so it reads clearly but can be slow on big tables.
Many subqueries can also be written as joins. Both are correct; choose whichever reads more clearly.