PostgreSQL
Subqueries, EXISTS y LATERAL
Subqueries escalares, correlacionadas, EXISTS y LATERAL para expresar dependencias entre consultas sin duplicar trabajo ni perder claridad.
- Última actualización
- Actualizada
- Nivel
- Aplicación
PostgreSQL
Subqueries escalares, correlacionadas, EXISTS y LATERAL para expresar dependencias entre consultas sin duplicar trabajo ni perder claridad.
Una subquery crea una relación o valor intermedio dentro de otra sentencia. EXISTS expresa presencia sin multiplicar filas; LATERAL permite que una fuente dependa de columnas producidas antes en FROM.
Una subquery puede producir:
un valor
→ scalar subquery
una fila
→ row subquery
un conjunto de filas y columnas
→ table subqueryNo es automáticamente más lenta que un join. PostgreSQL puede transformar, inlinear, decorrelacionar o ejecutar de forma diferente según semántica, estadísticas y versión.
La decisión principal debe ser expresar la intención:
necesito columnas relacionadas
→ JOIN
solo necesito saber si existe
→ EXISTS
necesito un valor único por fila
→ scalar subquery o LATERAL
necesito top N por cada fila externa
→ LATERAL o window functionUna consulta compleja necesita resultados intermedios. Sin subqueries, el backend podría ejecutar varias consultas o escribir joins que cambian la cardinalidad.
Ejemplo: encontrar customers con al menos una order pendiente. Un JOIN devuelve una fila por order y obliga a deduplicar al customer. EXISTS conserva una fila por customer.
SELECT
p.id,
p.name,
(
SELECT max(pp.valid_from)
FROM product_prices pp
WHERE pp.product_id = p.id
) AS last_price_change
FROM products p;La subquery está correlacionada porque usa p.id. La aggregate garantiza una fila; si no hay prices, devuelve NULL.
Una scalar subquery sin aggregate debe devolver máximo una fila:
SELECT (
SELECT price
FROM product_prices
WHERE product_id = $1
);Si devuelve más de una, PostgreSQL produce error. Añadir LIMIT 1 sin ORDER BY oculta el problema y elige una fila arbitraria.
SELECT *
FROM products
WHERE price > (
SELECT avg(price)
FROM products
);La media no depende de la fila externa. PostgreSQL puede calcularla una vez como InitPlan.
Si necesitas el promedio por category, la correlación cambia:
SELECT p.*
FROM products p
WHERE p.price > (
SELECT avg(p2.price)
FROM products p2
WHERE p2.category_id = p.category_id
);Compara esta forma con una aggregate + join o window function. El planner puede no decorrelacionar todos los casos.
SELECT totals.customer_id, totals.total
FROM (
SELECT customer_id, sum(total_amount) AS total
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
) AS totals
WHERE totals.total > 1000000;La subquery cambia granularidad a una fila por customer. La consulta externa filtra el resultado agregado.
Un alias es obligatorio para una subquery en FROM.
SELECT c.id, c.name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
AND o.status = 'pending'
);Flujo lógico:
La lista SELECT 1 es convencional; EXISTS no necesita materializar esas columnas.
SELECT p.id, p.name
FROM products p
WHERE NOT EXISTS (
SELECT 1
FROM inventory i
WHERE i.product_id = p.id
AND i.branch_id = $1
);Expresa un anti-join: products sin inventory para la branch.
Ventaja sobre NOT IN: no queda contaminado por un NULL de la subquery.
WHERE customer_id IN (
SELECT id
FROM customers
WHERE status = 'active'
)Es legible para pertenencia. PostgreSQL puede convertirlo en semi-join.
Con NULL:
IN puede producir UNKNOWN si no hay coincidencia y el conjunto contiene NULL.NOT IN, ese UNKNOWN suele producir resultados sorprendentes.price > ANY($1::numeric[])Es TRUE si la comparación es TRUE para algún elemento.
= ANY(array) es equivalente conceptual a pertenencia.
price > ALL($1::numeric[])Es TRUE si supera todos los elementos.
Arrays vacíos:
x = ANY('{}') → FALSE
x = ALL('{}') → TRUEpor lógica vacía. Define si el API debe aceptar listas vacías.
WHERE (created_at, id) < ($1, $2)Compara tuplas lexicográficamente y es útil para keyset pagination.
También:
WHERE (tenant_id, customer_id) IN (
SELECT tenant_id, id FROM customers WHERE status = 'active'
)Los tipos y cantidad de columnas deben coincidir.
Sin LATERAL, una subquery de FROM no puede referirse normalmente a columnas de una fuente anterior.
SELECT
p.id,
latest.price,
latest.valid_from
FROM products p
LEFT JOIN LATERAL (
SELECT pp.price, pp.valid_from
FROM product_prices pp
WHERE pp.product_id = p.id
ORDER BY pp.valid_from DESC, pp.id DESC
LIMIT 1
) latest ON true;Flujo:
p.id.Con índice (product_id, valid_from DESC, id DESC), puede resolverse mediante búsquedas rápidas.
SELECT
c.id AS customer_id,
recent.id AS order_id,
recent.created_at
FROM customers c
CROSS JOIN LATERAL (
SELECT o.id, o.created_at
FROM orders o
WHERE o.customer_id = c.id
ORDER BY o.created_at DESC, o.id DESC
LIMIT 3
) recent;Produce hasta tres orders por customer.
Alternativa con window function:
SELECT *
FROM (
SELECT
o.*,
row_number() OVER (
PARTITION BY customer_id
ORDER BY created_at DESC, id DESC
) AS position
FROM orders o
) ranked
WHERE position <= 3;Decisión:
Funciones set-returning en FROM pueden usar columnas anteriores:
SELECT p.id, tag
FROM products p
CROSS JOIN LATERAL unnest(p.tags) AS tag;Para JSONB:
SELECT e.id, item
FROM events e
CROSS JOIN LATERAL jsonb_array_elements(e.payload->'items') AS item;Expandir arrays/documentos multiplica filas. Limita volumen y valida shape.
Una subquery correlacionada puede parecer un N+1 lógico:
SELECT p.id,
(SELECT count(*) FROM order_items oi WHERE oi.product_id = p.id)
FROM products p;PostgreSQL puede ejecutarla repetidamente. Con índice puede ser aceptable; para muchos products una aggregate + join puede ser mejor:
WITH counts AS (
SELECT product_id, count(*) AS uses
FROM order_items
GROUP BY product_id
)
SELECT p.id, COALESCE(c.uses, 0)
FROM products p
LEFT JOIN counts c ON c.product_id = p.id;No optimices por intuición: revisa plan y tiempos.
Produce un valor por fila. Riesgo de repeated execution.
Expresa pertenencia, existencia o comparación.
Produce una relación intermedia y puede cambiar granularidad.
Puede alimentar INSERT, UPDATE o DELETE:
DELETE FROM sessions
WHERE user_id IN (
SELECT id FROM users WHERE deleted_at IS NOT NULL
);Para NULL y grandes conjuntos, USING o EXISTS puede expresar mejor.
Una CTE da nombre y puede reutilizarse:
WITH totals AS (...)
SELECT ... FROM totals;En versiones modernas, una CTE SELECT side-effect-free puede inlinearse. MATERIALIZED / NOT MATERIALIZED influyen. La nota siguiente desarrolla esto.
SELECT c.id, c.name
FROM customers c
WHERE c.tenant_id = $1
AND NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.tenant_id = c.tenant_id
AND o.customer_id = c.id
AND o.created_at >= $2
)
ORDER BY c.id;Decisiones:
(tenant_id, customer_id, created_at) puede ayudar.Devuelve NULL.
Error, no elige automáticamente.
Puede devolver ninguna fila inesperadamente.
CROSS elimina parent; LEFT conserva con NULL.
Una fila puede expandirse en miles y multiplicar trabajo.
La subquery solo ve filas permitidas al rol; EXISTS puede ser false aunque exista físicamente una fila invisible.
Puede multiplicar filas o cambiar NULL semantics.
Elige fila arbitraria.
UNKNOWN rompe el anti-join esperado.
Calcula el mismo trabajo varias veces; usa LATERAL o relación intermedia.
Añade complejidad sin valor.
Genera búsquedas repetidas costosas.
CTEs y consultas recursivas da nombre a relaciones intermedias, controla materialización y recorre estructuras jerárquicas con estado explícito.