¿Qué es la cláusula WITH en SQL?

En este artículo, aprenderás sobre la cláusula WITH de SQL, también conocida como expresión de tabla común (CTE). Veremos ejemplos para demostrar algunos de sus casos de uso a la luz de sus beneficios.

Introducción a la cláusula WITH de SQL

La cláusula WITH se introdujo en SQL estándar para simplificar las consultas largas y complejas, especialmente aquellas con JOIN y subconsultas. A menudo llamada indistintamente CTE o refactorización de subconsultas, una cláusula WITH define un conjunto temporal de datos cuya salida está disponible para ser referenciada en consultas posteriores.

La mejor manera de aprender la cláusula WITH en SQL es a través de la práctica. Recomiendo el curso interactivo de LearnSQL.esConsultas recursivas y expresiones de tabla comunes. Contiene más de 100 ejercicios que enseñan la cláusula WITH empezando por lo básico y progresando hasta temas avanzados como las consultas WITH recursivas.

La cláusula WITH se considera "temporal" porque el resultado no se almacena permanentemente en ningún lugar del esquema de la base de datos. Actúa como una vista temporal que sólo existe mientras dura la consulta, es decir, sólo está disponible durante el ámbito de ejecución de las sentencias SELECT, INSERT, UPDATE, DELETE o MERGE. Sólo es válida en la consulta a la que pertenece, lo que permite mejorar la estructura de una sentencia sin contaminar el espacio de nombres global.

La cláusula WITH se utiliza en las consultas en las que no es adecuada una tabla derivada. Por ello, se considera una alternativa más ordenada a las tablas temporales. En pocas palabras, la principal ventaja de la cláusula WITH es que ayuda a organizar y simplificar las consultas jerárquicas largas y complejas al dividirlas en trozos más pequeños y legibles.

La cláusula WITH se introdujo en el estándar SQL por primera vez en 1999 y ahora está disponible en todos los principales RDBMS. Algunas aplicaciones comunes de la CTE de SQL son

• Hacer referencia a una tabla temporal varias veces en una sola consulta.
• Realizar agregaciones de varios niveles, como encontrar la media de los máximos.
• Realizar un cálculo idéntico varias veces en el contexto de una consulta más amplia.
• Utilizar como alternativa a la creación de una vista en la base de datos.

A continuación, veamos un ejemplo rápido y sencillo de la cláusula WITH utilizando la tabla OrderDetails de la conocida base de datos Northwind. El objetivo es devolver la cantidad media pedida por ProductID:

CONSULTA:

WITH cte_quantity
AS
(SELECT
	SUM(Quantity) as Total
FROM OrderDetails
GROUP BY ProductID)

SELECT
	AVG(Total) average_product_quantity
FROM cte_quantity;

RESULTADO:

Número de registros: 1

Si se ejecutara sin la cláusula WITH y en su lugar se utilizara una subconsulta, la consulta sería algo así

CONSULTA:

SELECT
	AVG(Total) average_product_quantity
FROM
(SELECT
SUM(Quantity) as Total
FROM OrderDetails
GROUP BY ProductID)

Aunque no veas muchas diferencias tangibles entre ambas, la estructura desglosada que facilita una cláusula WITH será inestimable a medida que tus consultas aumenten de tamaño y jerarquía. A continuación, veremos un ejemplo de esto en forma de cláusula WITH anidada. Puedes encontrar más ejemplos en uno de nuestros artículos anteriores sobre el tema: CTE explicadas con ejemplos.

Sintaxis de la cláusula WITH

La secuencia general de pasos para ejecutar una cláusula WITH es:

1. Iniciar la cláusula WITH
2. Especificar el nombre de la expresión para la consulta a definir.
3. Opcional: Especificar los nombres de las columnas, separados por comas.
4. Después de asignar el nombre de la expresión, introduce el comando AS. Las expresiones, en este caso, son los conjuntos de resultados con nombre que utilizarás más adelante en la consulta principal para referirte a la CTE.
5. Escribir la consulta necesaria para producir el conjunto de datos temporal deseado.
6. Si trabajas con más de una CTE o cláusulas WITH, inicia cada uno de ellas separándolas por una coma y repite los pasos 2-4. Esta disposición también se denomina cláusula anidada WITH.
7. Haz referencia a las expresiones definidas anteriormente en una consulta posterior utilizando SELECT, INSERT, UPDATE, DELETE, o MERGE

La sintaxis para implementar una cláusula WITH se muestra en el pseudocódigo siguiente:

--CTE
WITH expression_name_1 (column_1, column_2,…,column_n)
AS
(CTE query definition 1),
expression_name_2 (column_1, column_2,…,column_n)
AS
(CTE query definition 2)

--Final query using CTE
SELECT expression_A, expression_B, ...
FROM expression_name_2

La cláusula WITH es un sustituto de las subconsultas normales. La única diferencia es que se puede reutilizar el mismo conjunto de resultados derivados varias veces en el código al utiliza la cláusula WITH para generar una CTE. No se puede hacer lo mismo con las subconsultas.

Como vemos arriba, los parámetros clave de ejecución para una cláusula WITH son:

• WITH: Se utiliza para crear una CTE, o el conjunto o conjuntos de datos temporales.
• expression_name (column_1, …, column_n)( column_1 ): El nombre del conjunto de datos temporal virtual que se utilizará en la consulta principal, y column_1 a column_n son los nombres de las columnas que se pueden utilizar en los pasos de consulta posteriores.
• AS (....): Esta sección define la consulta que llenará la CTE expression_name. Si se implementa una CTE anidada, la consulta dentro del segundo AS probablemente se referirá a la primera CTE.
• SELECT expression_A, expression_B FROM expression_name: Especifica la consulta externa principal en la que se utiliza la sentencia SELECT (o las sentencias INSERT, UPDATE, DELETE o MERGE) en uno o más de las CTE generadas para dar salida posteriormente al resultado previsto.

Todos los parámetros mencionados anteriormente son obligatorios. Puedes optar por utilizar las cláusulas WHERE, GROUP BY, ORDER BY y/o HAVING según sea necesario.

Cuando se ejecuta una consulta con una cláusula WITH, primero se evalúa la consulta mencionada en la cláusula y el resultado de esta evaluación se almacena en una relación temporal. A continuación, la consulta principal asociada a la cláusula WITH se ejecuta finalmente utilizando la relación temporal producida.

Este ejemplo demostrará una cláusula anidada WITH utilizando la misma tabla anterior OrderDetails. Una cláusula WITH anidada, o CTE anidadas, implican dos CTE dentro de la misma consulta, la segunda haciendo referencia a la primera.

OBJETIVO: Devolver el promedio de pedidos, o ventas realizadas, por EmployeeID para ShipperID 2 y ShipperID 3.

CONSULTA:

--1st CTE
WITH cte_sales
AS
(SELECT
	EmployeeID,
	COUNT(OrderID) as Orders,
	ShipperID
FROM Orders
GROUP BY EmployeeID, ShipperID),

--2nd CTE (nested)
shipper_cte
AS
(SELECT *
FROM cte_sales
WHERE ShipperID=2 or ShipperID=3)

--Query using CTE
SELECT
	ShipperID, AVG(Orders) average_order_per_employee
FROM
shipper_cte
GROUP BY ShipperID;

RESULTADO:

Número de registros: 2

En este caso, calculamos el número medio de pedidos por empleado pero sólo para ShipperID 2 y ShipperID 3. En la primera CTE, cte_sales se cuenta el número de pedidos y se agrupa por EmployeeID y ShipperID. En la segunda CTE shipper_cte hacemos referencia a la primera CTE y definimos las condiciones de ShipperID mediante una cláusula WHERE. Luego, en la consulta principal, sólo hacemos referencia a la segunda CTE shipper_ctepara calcular la media de pedidos por empleado en ShipperID.

Otros matices de la sintaxis asociada a las cláusulas WITH de SQL y a las CTE se detallan en el Módulo 2 de Consultas recursivas y expresiones de tabla comunes que también contiene una colección de ejemplos más avanzados.

Casos de uso de la cláusula WITH de SQL

Entonces, ¿cuándo se necesita realmente usar una cláusula WITH? Bueno, existen algunos casos de uso únicos. La mayoría están orientados a la comodidad, y a la facilidad de desarrollo y mantenimiento de las consultas.

Las aplicaciones más destacadas y los beneficios asociados de las CTE de SQL se pueden resumir del siguiente modo:

• Mejora la legibilidad del código: La programación literaria es un enfoque introducido por Donald Kuth, que tiene como objetivo organizar el código fuente en el orden de la lógica humana de tal manera que pueda ser entendido con un mínimo esfuerzo, al leerlo como una novela de modo secuencial. La cláusula WITH de SQL ayuda a conseguirlo creando tablas virtuales con nombre y dividiendo los cálculos grandes en partes más pequeñas. Estas partes pueden combinarse más tarde en la consulta en el SELECT final, o en otra sentencia, en lugar de agruparlas todas en un gran trozo.
• Mejora la capacidad de mantenimiento del código: La capacidad de mantenimiento va de la mano de la legibilidad. A medida que las consultas y las bases de datos aumentan con el tiempo, siempre será necesario depurar y solucionar problemas. ¡Un código más fácil de leer es más fácil de mantener!
• Alternativa a una vista: Las CTE pueden sustituir a las vistas y pueden SELECT (seleccionar), INSERT (insertar), UPDATE (actualizar), DELETE (eliminar) o MERGE (combinar). Esto puede resultar particularmente útil si no se tienen los derechos del sistema para crear un objeto de vista o si no se quiere crear una vista sólo para ser usada en una única consulta.
• Superar las limitaciones de las sentencias: Las CTE ayudan a superar restricciones como las limitaciones de la sentencia SELECT, por ejemplo, utilizar GROUP BY mediante funciones no deterministas.
• Procesamiento de estructuras jerárquicas: Esta es una de las aplicaciones más avanzadas de la CTE y se logra a través de lo que se conoce como CTE recursivas. Las consultas recursivas pueden llamarse a sí mismas, lo que permite atravesar modelos jerárquicos complejos. Más adelante se habla de ello.

Hay algunos casos más de uso de la CTE que se discuten en uno de nuestros artículos anteriores, "Cuándo usar CTE". El curso Consultas recursivas y expresiones de tabla comunes en LearnSQL.es te ayudará a desglosar todo el tema sobre CTE con recorridos detallados, para dominar el tópico con ejercicios prácticos.

Cláusula recursiva WITH

Los ejemplos anteriores utilizan cláusulas WITH no recursivas. Las CTE recursivas son una de las funcionalidades más avanzadas de la cláusula WITH, que permite referenciarse a sí misma dentro de esa CTE. Esto facilita considerablemente a los usuarios el recorrido por estructuras de datos jerárquicas complejas, como listas de materiales y organigramas.

Si ya tienes familiaridad con la recursión en programación, la función recursiva de la cláusula WITH incorpora de forma similar un caso base y el paso recursivo.

Reflexiones finales sobre la cláusula WITH de SQL

En este artículo, hemos recorrido la sintaxis básica y algunos ejemplos de cómo y cuándo se pueden definir y utilizar las cláusulas WITH o CTE. Para entender mejor su implementación en tu propio código, ¡la práctica es la clave! Para ello, te sugiero el curso Consultas recursivas y expresiones de tabla comunes de LearnSQL.es. En este curso, encontrarás más ejemplos prácticos y aplicaciones únicas de la cláusula WITH, con tutoriales interactivos sobre cómo utilizar las CTE (recursivas y no recursivas) en tu entorno de trabajo diario.

Una vez que sepas manejar las cláusulas WITH, te sorprenderás de lo bien que pueden quedar tus scripts SQL.