Subconsultas: escalares, derivadas y correlacionadas

Intermedio

Una subconsulta es una consulta dentro de otra. Si devuelve un solo valor es escalar y se usa como una columna o en una comparación. Si va en el FROM es una tabla derivada. Si usa columnas de la consulta de afuera es correlacionada: se evalúa una vez por cada fila, como en EXISTS.

Sintaxis

-- Escalar: un solo valor
WHERE importe > (SELECT avg(importe) FROM tabla)

-- Tabla derivada: un resultado intermedio en el FROM
FROM (SELECT cliente_id, count(*) AS n FROM pedidos GROUP BY cliente_id) AS t

-- Correlacionada: depende de la fila de afuera
WHERE EXISTS (SELECT 1 FROM hija AS h WHERE h.padre_id = p.id)

Ejemplo

SELECT
  a.name AS artista,
  a.country AS pais,
  (
    SELECT count(*)
    FROM follows AS f
    WHERE f.artist_id = a.id
  ) AS seguidores
FROM artists AS a
WHERE a.genre = 'cumbia'
  AND EXISTS (
    SELECT 1
    FROM albums AS al
    WHERE al.artist_id = a.id
      AND al.released_on >= '2024-07-01'
  )
ORDER BY seguidores DESC, a.name;

Resultado

3 filas · Ritmo

3 filas · Ritmo
artista	pais	seguidores
Cieloika	AR	32
Los Faroles Errante	PE	23
Vidrio sin Nombre	AR	15

Cómo leerlo

Son los artistas de cumbia que publicaron al menos un álbum desde julio de 2024, con su cantidad de seguidores. Las dos subconsultas son correlacionadas: seguidores es escalar (una cuenta por artista) y EXISTS solo pregunta si hay al menos un álbum reciente, sin traer filas ni multiplicar artistas con varios álbumes.

Error común

Así no

SELECT a.name,
  (SELECT al.title FROM albums AS al WHERE al.artist_id = a.id) AS album
FROM artists AS a;

Usar como escalar una subconsulta que puede devolver varias filas. Con un artista que tiene dos álbumes, PostgreSQL corta la consulta con el error «more than one row returned by a subquery used as an expression». Una subconsulta escalar debe garantizar una sola fila: un agregado (count, max) o un filtro por clave única.

En otros motores: MySQL, SQL Server, Oracle

MySQLSQL Server
Toda tabla derivada en el FROM necesita un alias (AS t). PostgreSQL lo exige hasta la versión 15; desde la 16 es opcional, pero ponerlo siempre hace la consulta portable.
Oracle
No acepta AS antes del alias de una tabla o de una subconsulta en el FROM: se escribe FROM (SELECT ...) t. En las columnas del SELECT el AS sí funciona.

Estos fragmentos no se ejecutan en el curso: se muestran como referencia de cada motor.

-- Escalar: un solo valor WHERE importe > (SELECT avg(importe) FROM tabla) -- Tabla derivada: un resultado intermedio en el FROM FROM (SELECT cliente_id, count(*) AS n FROM pedidos GROUP BY cliente_id) AS t -- Correlacionada: depende de la fila de afuera WHERE EXISTS (SELECT 1 FROM hija AS h WHERE h.padre_id = p.id)

SELECT a.name AS artista, a.country AS pais, ( SELECT count(*) FROM follows AS f WHERE f.artist_id = a.id ) AS seguidores FROM artists AS a WHERE a.genre = 'cumbia' AND EXISTS ( SELECT 1 FROM albums AS al WHERE al.artist_id = a.id AND al.released_on >= '2024-07-01' ) ORDER BY seguidores DESC, a.name;

artista

pais

seguidores

Cieloika

Los Faroles Errante

Vidrio sin Nombre