En PostgreSQL, una subconsulta correlacionada es una subconsulta que hace referencia a las columnas de la consulta externa.

A diferencia de una subconsulta regular, PostgreSQL evalúa la subconsulta correlacionada una vez por cada fila procesada por la consulta externa.

Dado que PostgreSQL reevalúa la subconsulta correlacionada para cada fila de la consulta externa, esto puede conducir a problemas de rendimiento, especialmente cuando se trata de grandes conjuntos de datos.

Una subconsulta correlacionada puede ser útil cuando se necesita realizar una consulta que depende de los valores de la corriente que se está procesando.v

Ejemplo de subconsulta correlacionada¶

Utilizaremos la tabla film de la base de datos de ejemplo para la demostración:

El siguiente ejemplo utiliza una subconsulta correlacionada para encontrar las películas con una duración superior a la media para sus respectivas calificaciones:

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

Salida:

 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
...

Cómo funciona.

La consulta externa recupera id, título, duración y calificación de la tabla film que tiene el alias f:

SELECT film_id, title, length, rating
FROM film f
WHERE length > (...)

Para cada fila procesada por la consulta externa, la subconsulta correlacionada calcula la longitud media de las películas que tienen la misma calificación que la fila actual (f.calificación).

La cláusula WHERE (WHERE length > (...)) comprueba si la duración de la película actual es superior a la media.

La subconsulta correlacionada calcula la duración [media]de las películas con la misma calificación que la fila actual en la consulta externa:

SELECT AVG(length)
FROM film
WHERE rating = f.rating

La cláusula WHERE garantiza que la subconsulta correlacionada sólo tenga en cuenta las películas con la misma calificación que la fila actual de la consulta externa. La condición rating = f.rating crea la correlación.

Como resultado, la consulta externa devuelve filas en las que la longitud de la película es mayor que la longitud media de las películas con la misma calificación.

Resumen¶

• Utilice una subconsulta correlacionada para realizar una consulta que dependa de los valores de la fila actual que se está procesando.