Correlated subquery

Use a subquery that references the outer query.

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.

Find products above their category average with a correlated subquery.

Show example solution

SELECT name FROM products p
WHERE price > (SELECT AVG(price) FROM products WHERE category = p.category);

Add p.category

SELECT name FROM products p
WHERE price > (SELECT AVG(price) FROM products WHERE category = ____);

Show answer

SELECT name FROM products p
WHERE price > (SELECT AVG(price) FROM products WHERE category = p.category);