Correlated subquery
- SQL course
- Intermediate
- Lesson 51/115
- 4 min
Use a subquery that references the outer query.
Correlated subquery
A correlated subquery uses columns from the outer query. It runs once per outer row.
SELECT name FROM products p
WHERE price > (SELECT AVG(price) FROM products WHERE category = p.category);This query returns products above their category average.
name is selected from products p. WHERE keeps only the rows where price > (SELECT AVG(price) FROM products WHERE category = … holds.
Correlated subquery — details
A correlated subquery depends on the outer query. p.category changes per row, so the subquery reruns.
For example, to compare each product with its own category's average price, you write WHERE category = p.category in the subquery.
Practice Correlated subquery
Find products above their category average with a correlated subquery.
Starter code
-- above category avg
SELECT name FROM products p
WHERE price > (SELECT AVG(price) FROM products WHERE category =Sample solution
SELECT name FROM products p
WHERE price > (SELECT AVG(price) FROM products WHERE category = p.category);Expected output
name Laptop Telefon
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Add p.category
SELECT name FROM products p
WHERE price > (SELECT AVG(price) FROM products WHERE category = ____);SELECT name FROM products p
WHERE price > (SELECT AVG(price) FROM products WHERE category = p.category);Explanation: p.category is the right answer.
2 Correlated subquery: match the terms
| Term | Meaning |
|---|---|
correlated |
outer-dependent |
p.category |
outer column |
AVG |
average |
reruns |
per row |
3 What does a correlated subquery use?
- GROUP BY
- A column from the outer query Correct answer
- Only constants
- DISTINCT
Explanation: A correlated subquery uses outer query columns.