Correlated Subquery

A Subquery that references a column of the outer query, so it is re-evaluated for each outer row.

sql
SELECT title FROM film f
WHERE rental_rate > (
  SELECT AVG(rental_rate) FROM film f2
  WHERE f2.rating = f.rating
);

When to use

In the reading: films above the average rental rate for their own rating, and EXISTS for films that have been rented. By contrast, PS 04 Q3's subquery does not depend on the outer query, so it is not correlated.

See PS 04.

Appears in