En esta página

Subconsultas y CTEs

14 min lectura TextoCap. 3 — Consultas avanzadas

Subconsultas y CTEs

A medida que las consultas se vuelven más complejas, necesitas herramientas para estructurarlas de forma legible y mantenible. Las subconsultas y los CTEs (Common Table Expressions) son esas herramientas: permiten descomponer problemas complejos en partes más pequeñas y comprensibles.

¿Qué es una subconsulta?

Una subconsulta (o subquery) es un SELECT anidado dentro de otra instrucción SQL. Puede aparecer en el SELECT, el FROM, el WHERE o el HAVING. El resultado de la subconsulta se usa como entrada para la consulta exterior.

Subconsultas en el WHERE

Subconsulta escalar

Una subconsulta escalar devuelve exactamente un valor (una fila, una columna). Se puede usar en cualquier lugar donde esperarías un valor concreto:

-- ¿Qué productos tienen un precio mayor que el precio promedio?
SELECT nombre, precio
FROM productos
WHERE precio > (
  SELECT AVG(precio)
  FROM productos
)
ORDER BY precio DESC;

-- ¿Cuántos productos tienen un precio mayor al máximo de la categoría 3 (libros)?
SELECT COUNT(*) AS productos_mas_caros_que_libros
FROM productos
WHERE precio > (
  SELECT MAX(precio)
  FROM productos
  WHERE categoria_id = 3
);

-- Subconsulta escalar en el SELECT: comparar cada producto con el promedio
SELECT
  nombre,
  precio,
  ROUND((SELECT AVG(precio) FROM productos), 2) AS precio_promedio,
  precio - (SELECT AVG(precio) FROM productos) AS diferencia_promedio,
  CASE
    WHEN precio > (SELECT AVG(precio) FROM productos) THEN 'Sobre el promedio'
    ELSE 'Bajo el promedio'
  END AS posicion
FROM productos
ORDER BY precio DESC;

Subconsulta con IN

-- Productos que han sido vendidos (tienen al menos una venta)
SELECT nombre, precio
FROM productos
WHERE id IN (
  SELECT DISTINCT producto_id
  FROM ventas
);

-- Clientes que han hecho pedidos en el último mes
SELECT nombre, email
FROM clientes
WHERE id IN (
  SELECT DISTINCT cliente_id
  FROM pedidos
  WHERE creado_en >= NOW() - INTERVAL '30 days'
    AND cliente_id IS NOT NULL
);

-- Clientes que NUNCA han pedido (NOT IN con subconsulta)
SELECT nombre, email
FROM clientes
WHERE id NOT IN (
  SELECT DISTINCT cliente_id
  FROM pedidos
  WHERE cliente_id IS NOT NULL  -- ¡CRÍTICO! NOT IN con NULLs produce resultados incorrectos
);

[!warning] ¡Trampa de NOT IN con NULLs! Si la subconsulta de NOT IN devuelve algún NULL, el resultado completo será vacío. Esto se debe a la lógica de tres valores de SQL: 1 NOT IN (2, NULL) evalúa a UNKNOWN (no TRUE). Siempre añade WHERE columna IS NOT NULL dentro de la subconsulta de NOT IN, o usa NOT EXISTS (que maneja NULLs correctamente).

EXISTS y NOT EXISTS

EXISTS es más eficiente que IN con subconsultas correlacionadas, porque se detiene en la primera coincidencia encontrada:

-- Clientes que tienen al menos un pedido (con EXISTS)
SELECT nombre, email
FROM clientes c
WHERE EXISTS (
  SELECT 1  -- El valor concreto no importa, solo si la subconsulta devuelve filas
  FROM pedidos p
  WHERE p.cliente_id = c.id  -- Correlación con la consulta exterior
);

-- Clientes SIN pedidos (con NOT EXISTS — maneja NULLs correctamente)
SELECT nombre, email
FROM clientes c
WHERE NOT EXISTS (
  SELECT 1
  FROM pedidos p
  WHERE p.cliente_id = c.id
);

-- Productos que nunca han sido vendidos
SELECT nombre, precio
FROM productos pr
WHERE NOT EXISTS (
  SELECT 1
  FROM ventas v
  WHERE v.producto_id = pr.id
);

Subconsultas correlacionadas

Una subconsulta correlacionada referencia columnas de la consulta exterior. Se ejecuta una vez por cada fila de la consulta exterior (puede ser lento en tablas grandes, considera convertirla a JOIN):

-- Para cada cliente, mostrar el total de su pedido más reciente
SELECT
  c.nombre,
  (SELECT p.total
   FROM pedidos p
   WHERE p.cliente_id = c.id
   ORDER BY p.creado_en DESC
   LIMIT 1) AS ultimo_pedido_total
FROM clientes c
ORDER BY c.nombre;

-- Productos cuyo precio es mayor que el promedio de su categoría
SELECT p.nombre, p.precio, p.categoria_id
FROM productos p
WHERE p.precio > (
  SELECT AVG(p2.precio)
  FROM productos p2
  WHERE p2.categoria_id = p.categoria_id  -- Correlación
)
ORDER BY p.categoria_id, p.precio DESC;

Subconsultas en el FROM (tablas derivadas)

Una subconsulta en el FROM actúa como una tabla temporal. Es útil cuando necesitas filtrar o transformar datos antes de hacer un JOIN:

-- Tabla derivada: estadísticas de ventas por producto
SELECT
  p.nombre,
  stats.num_ventas,
  stats.ingresos_totales
FROM productos p
JOIN (
  SELECT
    producto_id,
    COUNT(*) AS num_ventas,
    SUM(cantidad * precio_unit) AS ingresos_totales
  FROM ventas
  GROUP BY producto_id
) AS stats ON stats.producto_id = p.id
ORDER BY stats.ingresos_totales DESC;

-- Top 3 clientes por ingresos (usando tabla derivada)
SELECT *
FROM (
  SELECT
    c.nombre,
    SUM(p.total) AS total_gastado,
    RANK() OVER (ORDER BY SUM(p.total) DESC) AS ranking
  FROM clientes c
  JOIN pedidos p ON p.cliente_id = c.id
  GROUP BY c.id, c.nombre
) AS ranking_clientes
WHERE ranking <= 3;

CTEs — Common Table Expressions

Los CTEs, escritos con la cláusula WITH, son como "vistas temporales" que existen solo durante la ejecución de la consulta. Hacen que las consultas complejas sean mucho más legibles al nombrar partes intermedias:

-- CTE básico: equivale a la tabla derivada anterior, pero más legible
WITH estadisticas_ventas AS (
  SELECT
    producto_id,
    COUNT(*) AS num_ventas,
    SUM(cantidad * precio_unit) AS ingresos_totales,
    ROUND(AVG(precio_unit), 2) AS precio_promedio
  FROM ventas
  GROUP BY producto_id
)
SELECT
  p.nombre,
  ev.num_ventas,
  ev.ingresos_totales,
  ev.precio_promedio
FROM productos p
JOIN estadisticas_ventas ev ON ev.producto_id = p.id
ORDER BY ev.ingresos_totales DESC;

Múltiples CTEs encadenados

Puedes definir varios CTEs y referenciarlos entre sí o desde la consulta final:

-- Análisis completo de ventas con múltiples CTEs
WITH
-- CTE 1: Totales por cliente
totales_cliente AS (
  SELECT
    cliente_id,
    COUNT(*)          AS num_pedidos,
    SUM(total)        AS total_gastado,
    MAX(creado_en)    AS ultimo_pedido
  FROM pedidos
  WHERE cliente_id IS NOT NULL
  GROUP BY cliente_id
),

-- CTE 2: Segmentar clientes
segmentos AS (
  SELECT
    tc.cliente_id,
    tc.num_pedidos,
    tc.total_gastado,
    tc.ultimo_pedido,
    CASE
      WHEN tc.total_gastado >= 1000  THEN 'VIP'
      WHEN tc.total_gastado >= 300   THEN 'Regular'
      ELSE 'Ocasional'
    END AS segmento
  FROM totales_cliente tc
),

-- CTE 3: Clientes sin pedidos
sin_pedidos AS (
  SELECT id AS cliente_id
  FROM clientes
  WHERE id NOT IN (SELECT DISTINCT cliente_id FROM pedidos WHERE cliente_id IS NOT NULL)
)

-- Consulta final que usa los 3 CTEs
SELECT
  c.nombre,
  c.ciudad,
  COALESCE(s.segmento, 'Nuevo') AS segmento,
  COALESCE(s.num_pedidos, 0)    AS pedidos,
  COALESCE(s.total_gastado, 0)  AS gastado,
  s.ultimo_pedido
FROM clientes c
LEFT JOIN segmentos s ON s.cliente_id = c.id
ORDER BY COALESCE(s.total_gastado, 0) DESC;

CTEs recursivos

Los CTEs recursivos permiten consultar estructuras jerárquicas (árboles, grafos) de forma elegante. Tienen dos partes: el caso base y el caso recursivo:

-- Estructura jerárquica de empleados (de la lección anterior)
-- Consultar todos los subordinados directos e indirectos de un manager

WITH RECURSIVE jerarquia AS (
  -- Caso base: el empleado raíz (sin manager)
  SELECT
    id,
    nombre,
    manager_id,
    0 AS nivel,
    nombre AS ruta
  FROM empleados
  WHERE manager_id IS NULL

  UNION ALL

  -- Caso recursivo: empleados que reportan al nivel anterior
  SELECT
    e.id,
    e.nombre,
    e.manager_id,
    j.nivel + 1,
    j.ruta || ' → ' || e.nombre
  FROM empleados e
  JOIN jerarquia j ON e.manager_id = j.id
)
SELECT
  REPEAT('  ', nivel) || nombre AS organigrama,
  nivel,
  ruta
FROM jerarquia
ORDER BY ruta;

Serie de números con CTE recursivo

-- Generar una serie de números (útil para reportes con fechas continuas)
WITH RECURSIVE serie AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM serie WHERE n < 10
)
SELECT n FROM serie;

-- Generar todas las fechas del mes actual
WITH RECURSIVE dias_mes AS (
  SELECT DATE_TRUNC('month', CURRENT_DATE)::DATE AS dia
  UNION ALL
  SELECT (dia + INTERVAL '1 day')::DATE
  FROM dias_mes
  WHERE dia < DATE_TRUNC('month', CURRENT_DATE)::DATE + INTERVAL '1 month' - INTERVAL '1 day'
)
SELECT dia, TO_CHAR(dia, 'Day DD/MM') AS dia_formateado
FROM dias_mes;

[!warning] Protégete de recursión infinita: un CTE recursivo sin condición de terminación entrará en un bucle infinito. Siempre incluye una condición de parada en el caso recursivo. PostgreSQL tiene un límite de 100 iteraciones por defecto (configurable con SET max_recursion_depth). Si tu jerarquía puede tener ciclos (grafo, no árbol), añade un mecanismo de detección de ciclos.

CTEs vs subconsultas: ¿cuándo usar cada uno?

-- Las mismas consultas escritas como subconsulta y como CTE

-- Con subconsulta: más compacto para casos simples
SELECT nombre, precio
FROM productos
WHERE precio > (SELECT AVG(precio) FROM productos);

-- Con CTE: más legible cuando hay complejidad o reutilización
WITH precio_promedio AS (
  SELECT AVG(precio) AS promedio FROM productos
)
SELECT p.nombre, p.precio, pp.promedio AS precio_promedio_tienda
FROM productos p, precio_promedio pp
WHERE p.precio > pp.promedio;
Criterio Subconsulta CTE
Simplicidad Mejor para casos simples y únicos Mejor cuando el resultado se reutiliza
Legibilidad Puede volverse ilegible al anidar Mucho más legible con lógica compleja
Rendimiento Similar Similar (PostgreSQL puede materializar CTEs)
Recursividad No posible Sí con RECURSIVE
Depuración Difícil (todo en una sola expresión) Fácil (puedes consultar cada CTE individualmente)

[!tip] CTE como herramienta de depuración: cuando tengas una consulta compleja que no devuelve los resultados esperados, convierte las subconsultas en CTEs y ejecútalas individualmente. Esto te permite ver qué devuelve cada parte intermedia y aislar el problema. Una vez que todo funciona, puedes mantener el CTE (por legibilidad) o convertirlo de vuelta a subconsulta (si prefieres la compacidad).

Con subconsultas y CTEs tienes las herramientas para resolver cualquier problema analítico complejo. En la siguiente lección aprenderemos a crear vistas y funciones almacenadas en PL/pgSQL para encapsular lógica reutilizable directamente en la base de datos.

nextSteps:

  • slug: vistas-y-funciones label: "Siguiente: Vistas y funciones"