Uso de expresiones de tabla comunes
Las expresiones de tabla comunes (CTE) proporcionan un mecanismo para definir una subconsulta que luego se puede usar en otro lugar de una consulta. A diferencia de una tabla derivada, se define un CTE al principio de una consulta y se puede hacer referencia varias veces en la consulta externa.
Las CTE son expresiones con nombre definidas en una consulta. Al igual que las subconsultas y las tablas derivadas, los CTE proporcionan un medio para dividir los problemas de consulta en unidades más pequeñas y modulares. Las CTE están limitadas en el ámbito a la ejecución de la consulta externa. Cuando finaliza la consulta externa, también lo hace la duración del CTE.
Escritura de consultas con CTE para recuperar resultados
Puede usar CTE para recuperar los resultados. Para crear un CTE, se define en una cláusula WITH basada en la siguiente sintaxis:
WITH <CTE_name>
AS (<CTE_definition>)
Por ejemplo, para usar un CTE para recuperar información sobre los pedidos realizados por cada año por distintos clientes, podría ejecutar la consulta siguiente:
WITH CTE_year
AS
(
SELECT YEAR(orderdate) AS orderyear, custid
FROM Sales.Orders
)
SELECT orderyear, COUNT(DISTINCT custid) AS cust_count
FROM CTE_year
GROUP BY orderyear;
Asigne un nombre al CTE (denominado CTE_year) mediante la cláusula WITH y, a continuación, use AS () para definir la subconsulta. Después, puede hacer referencia a la CTE resultante en la consulta externa, que en este caso se realiza en la instrucción SELECT final (FROM CTE_year). El resultado tendría este aspecto:
| orderyear | cust_count |
|---|---|
| 2019 | 67 |
| 2020 | 86 |
| 2021 | 81 |
Al escribir consultas mediante CTE, tenga en cuenta las instrucciones siguientes:
- Los CTE requieren un nombre para la expresión de tabla, además de nombres únicos para cada una de las columnas a las que se hace referencia en la cláusula SELECT de CTE.
- Las CTE pueden usar alias en línea o externos para las columnas.
- A diferencia de una tabla derivada, se puede hacer referencia a un CTE varias veces en la misma consulta con una definición. También se pueden definir varias CTE en la misma cláusula WITH.
- Los CTE admiten la recursividad, en la que la expresión se define con una referencia a sí misma. Los CTE recursivos están fuera del ámbito de este módulo.