CTE vs Subconsulta en SQL - Tutorial con Ejemplos Interactivos

Cuando se trata de escribir SQL, es un dilema común: Comienzas con algo claro y simple que crece y crece hasta convertirse en un lío de subconsultas y lógica difícil de seguir.

Las Common Table Expressions (CTEs) o Expresiones de Tabla Común son una poderosa característica de SQL que te permite organizar y simplificar consultas grandes.

A lo largo de esta guía, encontrarás ejercicios interactivos de SQL donde puedes practicar escribiendo tanto subconsultas como CTEs para resolver los mismos problemas. Estos ejemplos prácticos te ayudarán a entender cuándo usar cada enfoque y experimentar de primera mano los beneficios de legibilidad de los CTEs.

¿Qué es un CTE (Common Table Expression)?

Un CTE es una forma de crear una especie de tabla temporal que solo existe durante el tiempo que tarda tu consulta en ejecutarse.

En Postgres defines un CTE usando la palabra clave WITH.

-- definir un CTE
WITH personas_que_les_gusta_el_queso AS (SELECT nombre, apellido, trabajo FROM personas WHERE le_gusta_queso = true)


-- usar el CTE como una tabla normal
select * from personas_que_les_gusta_el_queso where nombre like 'Matt%';

No todas las bases de datos soportan CTEs, pero las más comunes presentan la palabra clave WITH. Usaremos Postgres para el resto de este tutorial, pero gran parte del SQL a continuación puede usarse en otras bases de datos también.

Ten en cuenta que un CTE no es una tabla real, y solo existe durante la ejecución de tu consulta, pero la salida de un CTE puede reutilizarse múltiples veces en tu consulta, a diferencia de las subconsultas. Esto tiene beneficios organizacionales y de eficiencia de ejecución.

Beneficios de los CTEs

Los CTEs brillan como una forma de…

1. Dividir tu problema SQL en partes más pequeñas.
2. Mantener tu SQL ordenado y legible
3. Reutilizar conjuntos de resultados múltiples veces (a diferencia de las subconsultas)

Comparar Subconsultas vs CTEs:

Primero, intenta resolver este problema con una subconsulta:

[[ error ]]

[[ testData.solution ]]

Ahora resuelve el mismo problema usando un CTE y compara la legibilidad:

[[ error ]]

[[ testData.solution ]]

Observa cómo el enfoque con CTE le da al cálculo del promedio un nombre claro (avg_total) que hace que la consulta principal sea más fácil de entender.

Un ejemplo práctico de CTE

Considera el siguiente caso de uso que encontré recientemente.
Tenemos dos tablas:

1. entregas
2. clientes

Cada cliente puede tener muchas entregas.

Respondiendo una pregunta simple (no se requiere CTE aquí)

Queremos saber: ¿Cuántas entregas ha tenido cada cliente en la última semana?

Aquí hay una consulta SQL simple para responder esta pregunta

-- conteo de entregas agrupado por fecha y cliente
SELECT d.created_at::date, c.name, count(1) 
FROM deliveries d
JOIN clients c on c.id = d.client_id
WHERE d.created_at >= date_trunc('day', now()) - INTERVAL '7 days' 
GROUP BY c.name, d.created_at::date
ORDER BY c.name DESC

El resultado se verá algo así:

Bastante bien para una consulta tan simple.

Adaptando la consulta a nuevos requisitos

¡Aparecen un par de requisitos de negocio salvajes!

Nuevos Requisitos de Negocio

1. Necesitamos incluir fechas con cero entregas.
2. Solo queremos los 5 mejores clientes por volumen.
3. Queremos excluirnos del informe (Beekeeper).

Esto no debería ser muy difícil, ¿verdad?

Agregando fechas con cero entregas

Intentemos abordar el #1 primero. Necesitaremos comenzar considerando cada fecha en nuestro rango de fechas, en lugar de usar solo las fechas proporcionadas en la tabla de entregas.

Parece que no hay una forma fácil de agregar esta información dinámicamente, más allá de crear una tabla calendario. Qué molestia.

Veamos si podemos combinar un CTE con una ingeniosa función de Postgres llamada generate_series para hacer exactamente lo que necesitamos en lugar de crear una nueva tabla.

-- creando un CTE para todos los días de la semana usando generate_series de Postgres
WITH weekdaysToUse AS (
  SELECT date 
  FROM generate_series(
    date_trunc('day', now()) - INTERVAL '7 days', 
    current_date,
    '1 day'::interval
  ) date)

Lo bueno de este enfoque es que ahora puedo usar el conjunto de resultados weekdaysToUse para cualquier CTE subsiguiente y también para la consulta final, y definitivamente lo necesitaremos.

Nos estamos acercando. Agreguemos de nuevo los joins a entregas y clientes, luego ejecutemos la consulta y veamos qué obtenemos.

WITH weekdaysToUse AS (
  SELECT date 
  FROM generate_series(
    date_trunc('day', now()) - INTERVAL '7 days', 
    current_date,
    '1 day'::interval
  ) date
)

SELECT wtu.date, c.name, count(1) as cnt
FROM weekdaysToUse wtu
LEFT JOIN deliveries d ON wtu.date = d.created_at::date
JOIN clients c ON c.id = d.client_id
GROUP BY wtu.date, c.name

Resultado:

Un momento, ¿dónde están mis filas con cero entregas?

Comenzamos la consulta con weekdaysToUse, pero solo obtendremos una fila devuelta donde tuvimos al menos una entrega de un cliente para ese día. Rayos.

Necesitamos una forma de llenar las filas donde un cliente no tuvo una entrega para ese día. Si tuviéramos eso, podríamos combinarlo con los resultados anteriores, y estaríamos listos.

Tal vez podamos intentar usar lo que hemos aprendido hasta ahora y construirlo con CTEs.

-- devuelve una fila por cada par fecha/cliente. Podemos usar esto para llenar los huecos
WITH gapFiller AS (
  SELECT wtu.date, c.name, 0 as cnt
  FROM weekdaysToUse wtu, clients c
  WHERE NOT EXISTS(
    SELECT 1 
    FROM deliveries d 
    WHERE d.created_at = wtu.date 
    and d.client_id = c.id
  )
)

Si tomamos ese conjunto de resultados y hacemos UNION ALL con las filas que no eran cero antes, estaremos listos.

2 y 3. Obteniendo solo los 5 mejores clientes que no somos nosotros

¿Recuerdas nuestro requisito de negocio sobre los mejores clientes que no somos nosotros?

Aquí hay una solución con subconsulta,

SELECT d.created_at::date, c.name, COUNT(1) 
FROM deliveries d
JOIN clients c ON c.id = d.client_id
WHERE d.created_at >= date_trunc('day', now()) - INTERVAL '7 days' 
AND c.id IN (
  SELECT c1.id FROM clients c1 
  JOIN deliveries d1 ON c1.id = d1.client_id
  WHERE d1.created_at >= date_trunc('day', now()) - INTERVAL '7 days' 
    AND c1.name <> 'Beekeeper'
  GROUP BY c1.id
  ORDER BY count(d1.id) DESC
  LIMIT 5
)
group by c.name, d.created_at::date
order by c.name desc

Contrastada con la solución CTE.

WITH topFiveClientsThisWeekThatArentUs AS (
SELECT c.id, c.name FROM clients c 
  JOIN deliveries d ON c.id = d.client_id
  WHERE d.created_at >= date_trunc('day', now()) - INTERVAL '7 days' 
    AND c.name <> 'Beekeeper'
  GROUP BY c.id
  ORDER BY COUNT(d.id) DESC
  LIMIT 5
)

SELECT d.created_at::date, c.name, count(1) 
FROM deliveries d
JOIN topFiveClientsThisWeekThatArentUs c ON c.id = d.client_id
WHERE d.created_at >= date_trunc('day', now()) - INTERVAL '7 days' 
GROUP BY c.name, d.created_at::date
ORDER BY c.name desc

El CTE te permite estructurar lógicamente tu pensamiento y mejora la legibilidad.

Dentro de 6 meses cuando otro desarrollador tenga que editar esta consulta, te agradecerá por usar un CTE.

También obtienes puntos extra por elegir un nombre inteligente para el CTE. El mío es largo, pero describe exactamente lo que es: los cinco mejores clientes esta semana que no somos nosotros.

Ahora para la gran revelación, pero primero, escribí una solución ingenua con subconsultas para el problema para demostrar la alternativa:

Resultado final: Soluciones con Subconsulta vs CTE

Aquí hay una solución con subconsulta para nuestro problema de negocio

SELECT d AS date, name AS clientName, cnt

FROM (SELECT d FROM generate_series(
  date_trunc('day', now()) - INTERVAL '7 days',
  current_date,
  '1 day'::interval
) d) as weekdaysToUse --los días a usar

JOIN ( --combinaciones cliente/entrega
  SELECT d.created_at::date, c.name, d.client_id, count(1) AS cnt
  FROM clients c
  LEFT JOIN deliveries d ON d.client_id = c.id
  WHERE d.created_at >= date_trunc('day', now()) - INTERVAL '7 days'
  GROUP BY d.created_at::date, c.name, d.client_id 
  
  UNION ALL
  
  SELECT weekdaysToUse.d, c.name, c.id, 0 AS cnt
  FROM clients c, (SELECT d FROM generate_series(
      date_trunc('day', now()) - INTERVAL '7 days', 
      current_date,
      '1 day'::interval
    ) d) AS weekdaysToUse
  WHERE NOT EXISTS(
      SELECT 1 FROM deliveries del 
      WHERE del.created_at::date = weekdaysToUse.d
        AND del.client_id = c.id
    ) --relleno de huecos
) AS deliveries

ON weekdaysToUse.d = deliveries.created_at

WHERE deliveries.client_id IN (
  SELECT c.id
  FROM deliveries d
  JOIN clients c on c.id = d.client_id
  WHERE d.created_at >= date_trunc('day', now()) - INTERVAL '7 days' 
    AND c.name <> 'Beekeeper'
  GROUP BY c.id
  ORDER BY cnt DESC LIMIT 5
) --solo los 5 mejores que no somos nosotros

ORDER BY date asc, clientName ASC

Ugh, todos los requisitos están mezclados y es difícil averiguar qué es importante.

También necesito repetir las matemáticas de fechas y generate_series en múltiples ubicaciones, ya que no puedo reutilizarlo sin un CTE, y no quiero escanear demasiados datos.

Ahora, finalmente, aquí está nuestra solución completa con CTEs.

WITH weekdaysToUse AS (
  SELECT date 
  FROM generate_series(
    date_trunc('day', now()) - INTERVAL '7 days', 
    current_date,
    '1 day'::interval
  ) date
),

topClients AS (
  SELECT c.id, c.name FROM clients c
    JOIN deliveries d ON c.id = d.client_id
    WHERE d.created_at >= date_trunc('day', now()) - INTERVAL '7 days' 
      AND c.name <> 'Beekeeper'
    GROUP BY c.id
    ORDER BY COUNT(d.id) DESC
    LIMIT 5
),

dayClientCombosWithData AS (
  SELECT wtu.date, c.name, count(1) AS cnt
  FROM weekdaysToUse wtu
  LEFT JOIN deliveries d ON wtu.date = d.created_at::date
  JOIN topClients c ON c.id = d.client_id
  GROUP BY wtu.date, c.name
),

gapFiller AS (
  SELECT wtu.date, c.name, 0 as cnt
  FROM weekdaysToUse wtu, topClients c
  WHERE NOT EXISTS(
    SELECT 1 FROM DELIVERIES d WHERE d.created_at = wtu.date
  )
)

SELECT merged.date, merged.name, max(merged.cnt)
FROM (
  SELECT date, name, cnt FROM dayClientCombosWithData
  UNION ALL
  SELECT date, name, cnt FROM gapFiller 
  ) merged
GROUP BY merged.date, merged.name
ORDER BY merged.date, merged.name

Esta consulta SQL basada en CTE es directa, fácil de entender y fácil de editar en el futuro. Es por eso que los CTEs son increíbles.

Practica con múltiples CTEs:

Ahora es tu turno de practicar encadenando múltiples CTEs juntos para lógica compleja:

[[ error ]]

[[ testData.solution ]]

Este ejemplo demuestra cómo los CTEs te permiten dividir problemas complejos en pasos claros y lógicos que se construyen uno sobre otro.