SQL CTEs: Una Visión Completa de las Expresiones Comunes de Tabla

Este artículo proporciona una guía detallada de todo lo que necesitas saber sobre las expresiones comunes de tabla (CTE), desde la definición básica hasta las consultas recursivas más complejas. Si la base de datos que utilizas admite las CTE, aquí tienes todo lo que necesitas para sacarles el máximo partido.

Las expresiones comunes de tabla -también llamadas CTE, cláusulas WITH o consultas recursivas (aunque este último nombre es en realidad una aplicación específica)- son una característica relativamente nueva de SQL. Su objetivo es simplificar las consultas complejas, haciéndolas más fáciles de leer, entender y mantener. Puede que quieras empezar leyendo qué es una expresión de tabla común para tener una visión completa. Después, abróchate el cinturón y prepárate, porque este artículo te conducirá a través de todos los diferentes recursos CTE (cursos, ejercicios, ejemplos y explicaciones) disponibles en nuestro LearnSQL.es sitio web.

Las CTE son útiles para dividir consultas grandes y complejas en partes más pequeñas y comprensibles, como las vistas, las subconsultas y las tablas temporales. Y, al igual que las vistas, las subconsultas y las tablas temporales, puedes combinar CTEs para obtener un resultado final. Sin embargo, la ventaja de las CTE es que no generan objetos que persistan u ocupen espacio en la base de datos (como las vistas y las tablas temporales). Además, son más fáciles de leer e interpretar que las subconsultas.

Si sigue nuestro Consultas recursivas y expresiones de tabla comunes curso, tendrás una visión completa de las CTE de SQL. En él, aprenderás la sintaxis de las CTE y las diferentes formas de utilizarlas. Obtendrá más de 100 ejercicios interactivos que podrá ejecutar libremente en nuestra base de datos en línea. Todo lo que necesitas es tener conocimientos básicos de SQL y un navegador con conexión a Internet.

Sintaxis CTE

La forma general de una CTE SQL comienza con una cláusula WITH seguida de una definición de consulta (simplemente una sentencia normal SELECT ) a la que se le asigna un nombre. Después de esa definición hay una sentencia SELECT que se refiere a la consulta CTE por el nombre que se le ha asignado, como si fuera una tabla o una vista. Por ejemplo:

WITH cte_name AS (cte_query_definition)
SELECT *
FROM cte_name;

La cláusula WITH encabeza la consulta y contiene una subconsulta a la que se asigna un nombre. La consulta principal (situada después de la cláusula WITH ) es una sentencia SELECT normal que podría utilizar la subconsulta con nombre tantas veces como fuera necesario.

Cuando se ejecuta una consulta que contiene CTEs, el motor de la base de datos ejecuta primero la(s) consulta(s) CTE, guardando los resultados de la consulta CTE hasta que termina de ejecutar la consulta completa; los resultados CTE podrían considerarse resultados intermedios. Utiliza esos resultados intermedios como tablas para componer el resultado final. A continuación, devuelve el resultado final y descarta los resultados intermedios que generó anteriormente.

Un ejemplo básico de CTE

Veamos un ejemplo sencillo. Usted tiene un esquema con tres tablas employee, divisiony payment. Necesita obtener una lista de los empleados con su paga máxima y la división a la que pertenece cada empleado. Puede crear un CTE que primero resuelva el pago máximo de cada empleado en una subconsulta llamada max_paid. A continuación, en la consulta principal SELECT, se une a max_paid con employee y division para obtener el resultado final:

WITH max_paid (employee_id, max_payment) AS (
	SELECT
		emp.employee_id,
		MAX(pay.payment) AS max_payment
	FROM
		employee AS emp
INNER JOIN payment AS pay ON
pay.employee_id = emp.employee_id
	GROUP BY
		emp.employee_id
	)
SELECT
	emp.employee_id,
	emp.name AS employee_name,
	div.name AS division_name,
	mp.max_payment
FROM max_paid mp
	INNER JOIN employee AS emp ON
		emp.employee_id = mp.employee_id
	INNER JOIN division AS div ON
		div.division_id = emp.division_id;

Cuando la base de datos ejecuta esta consulta, primero construye un conjunto de datos temporal con los resultados de la consulta CTE y lo denomina max_paid. Luego, en el SELECT externo, utiliza max_paid como si fuera una tabla. Se une max_paid con las tablas reales del esquema (employee y division) para construir el resultado final.

Para una comprensión más profunda de la sintaxis SQL CTE, recomiendo estos 5 ejemplos prácticos de cláusulas WITH.

Múltiples CTEs en una Única Consulta

Se puede utilizar más de una CTE en la misma consulta. La forma de hacerlo es simplemente separando cada definición de CTE con una coma:

WITH cte1 AS (
	SELECT ...
	FROM ...
),
cte2 AS (
	SELECT ...
	FROM ...
)
SELECT ...
FROM cte1 JOIN cte2, ...

Cada CTE puede hacer uso de otros CTE previamente definidos en la misma consulta; esto se denomina anidamiento. Esto permite a las CTE descomponer consultas grandes y complejas en subconsultas más pequeñas (y más manejables). Se puede resolver gradualmente cada parte de un problema hasta simplificarlo en uno final SELECT.

El siguiente ejemplo utiliza dos CTEs anidadas que obtienen información de una tabla llamada sales. La primera CTE SalesByDayRegionobtiene los totales de ventas por día y por región. El segundo, SalesByDayse basa en SalesByDayRegion para obtener los totales de ventas de cada día. El último SELECT une ambos CTEs para calcular el porcentaje de ventas de cada región en relación con los totales por día.

Esta es la consulta

WITH SalesByDayRegion AS (
	SELECT
		day,
		region,
		SUM(amount) AS AmountByDayRegion
	FROM 	Sales
	GROUP BY day, region
	),
	SalesByDay AS (
	SELECT
		day
		SUM(GroupedAmount1) AS AmountByDay
	FROM 	Sales
	GROUP BY day
	)
SELECT
	sdr.day,
	sdr.region,
	AmountByDayRegion
	AmountByDayRegion / AmountByDay AS Percentage
FROM SalesByDayRegion sdr 
INNER JOIN SalesByDay sd
ON sdr.day = sd.day;

Consultas recursivas y expresiones de tabla comunes

WITH Las cláusulas -es decir, los CTE- dan a SQL la capacidad de implementar la recursividad. Esto elimina la necesidad de hacerlo mediante otros lenguajes de programación, con lo que se consigue una mayor eficacia en la ejecución de consultas.

En cualquier lenguaje de programación, la recursividad se implementa haciendo que una función o procedimiento se llame a sí mismo repetidamente hasta que se cumpla una condición de terminación. Cada iteración recursiva añade o modifica los datos resultantes de la iteración anterior y proporciona sus resultados a la siguiente iteración. Cuando se cumple la condición de terminación, devuelve el resultado final.

En SQL, la recursividad es posible porque las CTE pueden referenciarse a sí mismas. En la mayoría de los dialectos de SQL (excepto Transact SQL), la palabra RECURSIVE se utiliza después de WITH para indicar recursión. Puedes leer más sobre qué es una CTE recursiva aquí.

Las CTEs recursivas se componen de los siguientes elementos: un miembro ancla, un miembro recursivo, una comprobación de terminación y una invocación.

El miembro ancla establece el punto de partida de la recursión. Esta parte de la ETC debe poder resolverse sin invocarse a sí misma. El miembro recursivo utiliza los resultados de una iteración anterior para procesar o añadir más datos a los resultados, obteniendo un nuevo conjunto de resultados para la siguiente iteración - o, cuando se cumple la condición de terminación, un conjunto de resultados para el miembro de invocación.

El miembro ancla y el miembro recursivo se combinan mediante un operador UNION. Esto significa que los conjuntos de resultados de ambos miembros deben tener la misma estructura de columnas: sus columnas deben estar en el mismo orden y deben tener los mismos tipos de datos.

En el siguiente ejemplo, utilizamos un CTE recursivo para calcular el factorial de los números del 1 al 5:

WITH RECURSIVE factorial(n, factorial) AS (
SELECT 1, 1
UNION ALL
SELECT n + 1, (n +1) * factorial FROM factorial WHERE n < 5
)
SELECT * FROM factorial;

En este ejemplo, el miembro ancla de la ETC recursiva es:

SELECT 1, 1

Y el miembro recursivo, junto con la condición de terminación, es el siguiente SELECT:

SELECT n + 1, (n +1) * factorial 
FROM factorial 
WHERE n < 5

La invocación es simplemente un SELECT * del CTE factorial. Observe que este SELECT trata factorial como si fuera una tabla. Si sólo nos interesa ver las tres primeras filas del resultado del cálculo factorial, podríamos añadir esto a la invocación:

SELECT * 
FROM factorial 
WHERE n <= 3;

Este ejemplo es extremadamente sencillo. Las CTEs recursivas pueden utilizarse para resolver necesidades más complejas, como recorrer un conjunto de datos que representa una estructura de árbol. Podría utilizarlo para explorar el organigrama de una empresa, como veremos en un ejemplo más adelante.

Si desea profundizar en la recursividad en SQL, consulte nuestro curso en líneaConsultas recursivas y expresiones de tabla comunes .

Ventajas de las expresiones comunes de tabla

Las expresiones comunes de tabla tienen muchas ventajas, desde mejorar la comprensibilidad de las consultas hasta atravesar estructuras de datos jerárquicas. Incluso se pueden hacer dibujos con SQL. Como punto de partida, te sugiero que leas los artículos ya mencionados, CTEs explicadas con ejemplos y 5 ejemplos prácticos de CTEs en SQL. Luego, sigue leyendo para ver cuándo es útil y efectivo usar CTEs.

Facilitar la Organización y Claridad de las Consultas

Las CTEs permiten resolver consultas complejas dividiéndolas en varias consultas más pequeñas, manejables y fáciles de leer. A su vez, la posibilidad de utilizar CTEs anidadas le permite avanzar gradualmente hacia una solución.

Una CTE es equivalente a una subconsulta, pero con una ventaja muy importante: puede darle un nombre a una CTE y utilizarla repetidamente en distintas partes de su consulta. Es como crear una vista, con la diferencia de que la vista es un objeto perenne de la base de datos. La CTE sólo existe mientras se ejecuta la consulta; después desaparece sin dejar rastro. El siguiente ejemplo muestra cómo crear una CTE para obtener datos de resumen de una tabla y, a continuación, utilizar esa CTE para crear una vista. sales y, a continuación, utilizar esa CTE para realizar diferentes consultas:

WITH TotalSalesByCategory AS (
	SELECT customer_category, SUM(amount) AS total_sales
	FROM sales
	GROUP BY customer_category
	)
SELECT 'big customers' AS grouping, SUM(total_sales) AS grand_total
FROM TotalSalesByCategory
WHERE customer_category IN ('A', 'B', 'C')
UNION
SELECT 'medium customers' AS grouping, SUM(total_sales) AS grand_total
FROM TotalSalesByCategory
WHERE customer_category IN ('D', 'E', 'F')
UNION
SELECT 'small customers' AS grouping, SUM(total_sales) AS grand_total
FROM TotalSalesByCategory
WHERE customer_category IN ('G', 'H', 'I');

Aprenda a desordenar sus consultas en estos artículos:

• Cómo Organizar Consultas SQL con CTEs
• Cómo Mejorar la Legibilidad de las Consultas con Expresiones Comunes de Tabla

Agregaciones Multinivel

Hemos visto diferentes opciones para incluir múltiples CTEs en la misma consulta. Al hacerlo, se multiplica el poder de las CTEs para reducir la complejidad de una consulta y mejorar su legibilidad. Las opciones para utilizar múltiples CTEs en SQL se reducen a:

1. Múltiples CTEs independientes.
2. CTEs anidados.
3. Utilizar una CTE recursiva junto con otras no recursivas.

Aprenda a aprovechar las ventajas de las CTEs combinadas con los siguientes artículos:

• Cómo Escribir Múltiples CTEs en SQL
• Cómo Utilizar 2 CTEs en una Única Consulta SQL
• ¿Se Pueden Utilizar Múltiples Sentencias WITH en SQL?

Consulta de Datos Jerárquicos

Los datos jerárquicos se caracterizan por tener una relación ordenada entre sus elementos. En general, los datos jerárquicos se organizan en niveles; se puede averiguar qué datos están "por encima" o "por debajo" de otras piezas de información.

En las bases de datos relacionales, las relaciones jerárquicas pueden establecerse dentro de una tabla haciendo que cada fila contenga un campo que permita relacionarla con una fila de nivel superior dentro de la misma tabla. De este modo, es posible construir estructuras de datos jerárquicas con filas "padre" y filas "hijo", que suelen representarse visualmente como una estructura en forma de árbol.

Los CTE también se utilizan para consultar datos jerárquicos. Descubra cómo hacerlo leyendo más sobre la consulta de datos jerárquicos en SQL.

Un ejemplo típico de estructura jerárquica de datos es el organigrama de una empresa, donde las filas de la tabla contienen datos de los empleados y cada empleado hace referencia a su jefe. Para más detalles, lea cómo encontrar todos los empleados de cada jefe en SQL.

Diviértase Dibujando Cuadros con CTEs

¿Busca una forma divertida de escribir consultas ingeniosas en SQL? No puedes hacer grandes obras de arte utilizando SQL, pero puedes hacer dibujos esquemáticos utilizando CTEs recursivas.

Antes hemos mencionado que puede utilizar la recursividad para recorrer estructuras de datos jerárquicas en forma de árbol, como un organigrama. Quizá le sorprenda saber que puede dibujar y recorrer estructuras jerárquicas con SQL. Para descubrirlo, lea este artículo sobre el uso de SQL para dibujar un árbol de Navidad bastante singular.

Alternativas a las CTE de SQL

Dejando a un lado la recursividad (que en SQL es una virtud única de las CTEs), el resto de los beneficios que proporcionan las CTEs se pueden conseguir con subconsultas, vistas y tablas temporales. Comparemos cada una de ellas con una CTE.

CTEs vs. Subconsultas

Las subconsultas en SQL deben escribirse entre paréntesis e incluirse con el resto de los elementos de la consulta principal. Esto significa que no aportan mucha claridad a la consulta. Las CTE se escriben por separado, al principio de la consulta y dentro de su propia cláusula WITH. Esto facilita mucho la lectura de la consulta. Si no te interesa ver el funcionamiento interno de la CTE, puedes pasar por alto la cláusula WITH y leer sólo la SELECT principal para entender los resultados.

Puedes obtener más detalles acerca de las diferencias entre CTEs y subconsultas leyendo este artículo que profundiza en las diferencias entre CTEs y subconsultas.

Además, las subconsultas no son reutilizables. Si desea utilizar el resultado de una subconsulta en diferentes partes de una consulta, tendrá que escribirla de nuevo cada vez. Mientras tanto, los CTEs pueden reutilizarse tantas veces como se desee en el SELECT principal o en otros CTEs de la misma consulta. Esto mejora la facilidad de lectura y el rendimiento de la consulta; cada CTE se ejecuta una sola vez y sus resultados están disponibles a lo largo de toda la consulta.

Por último, es bueno tener en cuenta que las subconsultas dentro de la cláusula FROM llevan un alias que se utiliza en el resto de la consulta para acceder a sus resultados. Pero otras subconsultas que se incluyen en la lista de columnas de una cláusula SELECT o WHERE no llevan alias. Las CTEs siempre llevan un nombre que se utiliza para referenciarlas en el resto de la consulta.

CTEs vs. Vistas

Las vistas tienen mucho en común con las CTE, pero existe una diferencia crucial entre ellas. Las vistas permanecen como objetos en la base de datos hasta que alguien las elimina. Las CTEs sólo existen en el contexto de su consulta. Una vez que la consulta termina de ejecutarse, la CTE desaparece. Lee este artículo sobre las diferencias entre CTEs y vistas para saber más.

CTEs vs. Tablas Temporales

Las tablas temporales también tienen muchas similitudes con las CTE. El ciclo de vida de una tabla temporal termina con la sesión de la base de datos en la que se creó la tabla temporal, por lo que no tiene que preocuparse de dejar objetos innecesarios abarrotando el esquema de la base de datos. Pero hay una diferencia fundamental: la creación de la tabla temporal debe hacerse en un comando separado de la consulta que la utiliza. No se puede crear una tabla temporal dentro de una consulta SELECT; si se hiciera, sería una subconsulta, no una tabla temporal.

Si creas una tabla temporal y luego te das cuenta de que necesitas añadirle una columna o cambiar cualquier otra cosa, deberás borrarla y volver a crearla. Con una CTE, sólo tiene que hacer el cambio en su definición y volver a ejecutar la consulta.

CTE en distintos SGBD

Las CTE aparecieron en el estándar SQL de 1999. Su implementación en cada uno de los sistemas gestores de bases de datos (SGBD) más populares fue la siguiente:

• PostgreSQL: versión 8.4, julio de 2009
• MS SQL Server: versión 2012, mayo de 2012
• Oracle Database: versión 9.2, junio de 2002
• MySQL: versión 8.0, abril de 2018
• MariaDB: versión 10.2.2, mayo de 2018

Google BigQuery también ofrece soporte para CTEs. Dado que se ejecuta en la Nube como un almacén de datos totalmente gestionado, basta con saber que actualmente ofrece soporte para CTEs.

La sintaxis SQL CTE es la misma para todos los DBMS. La única excepción es en MS SQL Server (T-SQL), donde la sintaxis para escribir un CTE recursivo no requiere el uso de la palabra clave RECURSIVE. Puede aprender más sobre CTEs en MS SQL Server leyendo sobre recursividad en SQL Server y viendo ejemplos de CTE en SQL Server.

Puesta en Práctica de sus Conocimientos sobre CTEs SQL

A lo largo de este artículo, ha aprendido todo sobre las expresiones comunes de tabla en SQL. Ahora necesitas poner ese conocimiento en práctica. La mejor manera de hacerlo es tomar uno de nuestros cursos específicos sobre CTEs y consultas recursivas, donde encontrarás toneladas de ejercicios interactivos.

Estos cursos están dirigidos a estudiantes, analistas de bases de datos intermedios y científicos de datos principiantes. El único requisito es cierto dominio de SQL básico como SELECT statements, JOINs, GROUP BY, etc.

Puede optar por nuestro curso estándar Consultas recursivas y expresiones de tabla comunes, que es independiente del dialecto SQL, o elegir un curso específico para el SGBD de su elección:

• Consultas recursivas y expresiones de tabla comunes en MS SQL Server
• Consultas recursivas y expresiones de tabla comunes en MySQL 8
• Consultas recursivas y expresiones de tabla comunes en PostgreSQL

Ninguno de estos cursos requiere que utilice sus propias bases de datos. Nuestra plataforma le proporciona una base de datos lista para usar, completa con el esquema y rellenada con información. Estos entornos de práctica son seguros; no tiene que preocuparse por cometer errores, ya que no hay posibilidad de romper nada.

Eche un vistazo a estos otros valiosos recursos para reforzar sus conocimientos de las consultas de WITH:

• Las 5 mejores preguntas SQL CTE para entrevistas
• 11 Ejercicios de Expresiones Comunes de Tabla SQL

La Importancia de Conocer las CTEs en SQL

El dominio de las expresiones comunes de tabla (CTEs) y las consultas recursivas de SQL comienza con la comprensión de sus fundamentos y luego progresa hacia aplicaciones más complejas. Nuestros recursos curados hacen que el aprendizaje de las CTE sea ameno y práctico.

Para mantener sus habilidades con las CTE, acostúmbrese a practicar a diario. Cuando se enfrente a consultas complejas, piense si el uso de una o varias CTE podría simplificar la tarea dividiéndola en partes más pequeñas y manejables. Pronto te encontrarás utilizando CTEs de forma rutinaria en tus tareas SQL.

Además, le recomiendo que eche un vistazo a nuestro cursoConsultas recursivas y expresiones de tabla comunes para mejorar su comprensión de las CTE de SQL. Le proporcionará las herramientas necesarias para enfrentarse con confianza incluso a las consultas más complejas. ¡Feliz aprendizaje!