Introduction to PostgreSQL correlated subquery

In PostgreSQL, a correlated subquery is asubquery that references the columns from the outer query.

Unlike a regular subquery, PostgreSQL evaluates the correlated subquery once for each row processed by the outer query.

Since PostgreSQL reevaluates the correlated subquery for every row in the outer query, this may lead to performance issues, especially when dealing with large datasets.

A correlated subquery can be useful when you need to perform a query that depends on the values of the current being processed.

PostgreSQL correlated subquery example

We’ll use thefilm table from thesample database for the demonstration:

The following example uses a correlated subquery to find the films with higher lengths than average for their respective ratings:

SELECT film_id, title,length, ratingFROM film fWHERE length > (    SELECT AVG(length)    FROM film    WHERE rating= f.rating);

Output:

film_id |            title            | length | rating---------+-----------------------------+--------+--------     133 | Chamber Italian             |    117 | NC-17       4 | Affair Prejudice            |    117 | G       5 | African Egg                 |    130 | G       6 | Agent Truman                |    169 | PG...

How it works.

The outer query retrieves id, title, length, and rating from thefilm table that has the aliasf:

SELECT film_id, title, length, ratingFROM film fWHERE length > (...)

For each row processed by the outer query, the correlated subquery calculates the averagelength of films that have the samerating as the current row (f.rating).

TheWHERE clause (WHERE length > (...)) checks if the length of the current film is greater than the average.

The correlated subquery calculates theaverage length for films with the same rating as the current row in the outer query:

SELECT AVG(length)FROM filmWHERE rating= f.rating

TheWHERE clause ensures that the correlated subquery considers only films with the same rating as the current row in the outer query. The conditionrating = f.rating creates the correlation.

As a result, the outer query returns rows where thelength of the film is greater than the averagelength for films with the samerating.

Summary

• Use a correlated subquery to perform a query that depends on the values of the current row being processed.

Last updated onJanuary 22, 2024

On this page

• Introduction to PostgreSQL correlated subquery
• PostgreSQL correlated subquery example
• Summary