Introducción a CTE: WITH

En programación, puedes separar un trozo de código y darle un nombre — crear una función. Con los CTE pasa igual. Puedes sacar un SELECT-subconsulta aparte del query principal, darle un nombre y luego usarlo en tu consulta SQL principal.

CTE (Common Table Expressions, o expresiones de tabla comunes) — es como un soplo de aire fresco para quien está cansado de las subconsultas anidadas. Hacen que el código SQL no solo sea entendible, sino también elegante de verdad. Si antes te peleabas con estructuras enormes de subconsultas y te dolían los ojos — es hora de conocer la "magia" de los CTE.

Imagina que construyes una casa. Normalmente quieres poner las ventanas y las puertas cuanto antes (o sea, meter subconsultas a saco), aunque las paredes aún no estén listas. Pero con CTE es diferente: primero haces un boceto limpio — creas una tabla temporal, como si fuera el plano de la casa. Y luego, paso a paso, vas levantando los pisos de la consulta. Con estilo, seguro y técnico.

En esencia, un CTE es una tabla virtual que creas al vuelo usando un SELECT. Es como una subconsulta, pero más pro. Si en programación puedes sacar lógica a una función con nombre claro, en SQL ese papel lo cumple el CTE. Escribes un SELECT, le das un nombre — y lo usas después como parte de una consulta grande y compleja. ¿Mola? ¡Pues claro!

Ejemplo de consulta SQL con subconsulta:

-- consulta principal
SELECT *
FROM (
    SELECT *
    FROM students
    WHERE grade > 75
) AS filtered_students; -- subconsulta que recibió el alias filtered_students

Subconsulta sacada aparte:

-- CTE/subconsulta que recibió el alias filtered_students
WITH filtered_students AS (
    SELECT *
    FROM students
    WHERE grade > 75
)

-- consulta principal
SELECT *
FROM filtered_students;

Flipante, pero las subconsultas aparecieron 20 años antes que los CTE. En el estándar SQL-89 ya había subconsultas, pero los CTE llegaron solo en SQL-2009.

Sintaxis de WITH

Un CTE empieza con la palabra clave WITH y se ve más o menos así:

WITH cte_name AS (
    SELECT ... -- tu consulta aquí
)

SELECT ...
FROM cte_name;

Aquí:

• cte_name — es el nombre de tu CTE. Puedes elegir cualquier nombre que tenga sentido, por ejemplo, high_scores, filtered_data o incluso best_students.
• Dentro de los paréntesis () va la consulta que prepara los datos para usarlos después.
• Después de definir el CTE puedes referenciarlo como si fuera una tabla normal en la consulta principal.

Ejemplo 1: CTE sencillo

Vamos a ver cómo funciona un CTE con un ejemplo real. Imagina que tienes una tabla students — una lista de estudiantes con sus notas:

Nuestro objetivo — seleccionar todos los estudiantes con nota mayor que 85 y mostrar sus datos.

Versión sin CTE:

Puedes hacerlo con una subconsulta:

SELECT *
FROM (
    SELECT *
    FROM students
    WHERE grade > 85
) AS filtered_students;

Y así, pero usando CTE — mucho más agradable a la vista:

WITH filtered_students AS (
    SELECT *
    FROM students
    WHERE grade > 85
)
SELECT *
FROM filtered_students;

¿A que sí? Se ve más limpio y claro. Hemos separado bien la preparación de datos (WITH) de la parte principal de la consulta (SELECT). Es como ordenar la mesa antes de currar — se respira mejor.

Ejemplo 2: Varios CTE

Puedes definir varios CTE en una sola consulta. Esto es muy útil si necesitas preparar los datos por etapas.

Dato: tabla grades, donde se guardan las notas de los estudiantes por curso:

Objetivo: para cada estudiante, calcular la nota media y luego seleccionar solo los que tienen media mayor que 85.

Solución con varios CTE:

WITH student_averages AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
),
high_achievers AS (
    SELECT student_id, avg_grade
    FROM student_averages 	-- usamos el primer CTE - student_averages
    WHERE avg_grade > 85
)

SELECT *
FROM high_achievers; -- usamos el segundo CTE - high_achievers

Aquí:

1. student_averages prepara los datos previos — las medias de los estudiantes.
2. high_achievers usa esos datos para seleccionar solo los que tienen media mayor que 85.

Diferencia entre CTE y subconsultas

Spoiler: los CTE no sustituyen a las subconsultas, pero a veces son mucho más cómodos.

Una subconsulta es una consulta dentro de otra consulta. Son útiles cuando quieres un resultado rápido, pero si usas muchas, el código se vuelve un caos.

Ejemplo:

SELECT *
FROM (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
) AS student_averages
WHERE avg_grade > 85;

Las subconsultas pueden ir dentro de SELECT, FROM, WHERE y HAVING. Además, pueden referenciar columnas de la consulta externa. Los CTE tienen problemas con esto último.

Por su parte, los CTE hacen el código mucho más legible, así que es más fácil de mantener y tiene menos bugs. En vez de anidar consultas, puedes simplemente "nombrar" el resultado de una subconsulta y usarlo después.

WITH student_averages AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
)

SELECT *
FROM student_averages
WHERE avg_grade > 85;

Los CTE son especialmente útiles si necesitas usar los datos preparados varias veces en la misma consulta.

¿Cuándo usar CTE?

• Cuando necesitas dividir una consulta compleja en varias etapas lógicas.
• Si la consulta debe ser legible y fácil de mantener. Nadie quiere pelearse con estructuras anidadas que parecen espaguetis.
• Para preparar datos temporales que solo va a usar la consulta actual.

Ejemplo final: análisis de cursos

Vamos a juntar todo lo aprendido:

1. Buscamos estudiantes con media alta.
2. Mostramos sus nombres y los cursos en los que están apuntados.

WITH student_averages AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
),
high_achievers AS (
    SELECT student_id
    FROM student_averages
    WHERE avg_grade > 85
),
student_courses AS (
    SELECT e.student_id, c.course_name
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
)

SELECT ha.student_id, sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id;

Fíjate cómo está todo estructurado:

1. Primero preparamos las medias.
2. Luego seleccionamos solo los mejores estudiantes.
3. Después los relacionamos con sus cursos.

Ahora sí, ya puedes empezar a usar CTE para crear consultas SQL bonitas, legibles y potentes.

¡Dale caña a tus propios proyectos!