Expresión de tabla común anidada (CTE) en el almacenamiento de datos de Fabric (Transact-SQL)
Se aplica a: 
Punto de conexión de SQL Analytics y Almacenamiento en Microsoft Fabric
Las expresiones de tabla comunes (CTE) pueden simplificar las consultas complejas mediante la deconstrucción de consultas normalmente complejas en bloques reutilizables.
Hay cuatro tipos de CTE, incluidos CTE estándar, secuencial, recursivo y CTE anidado .
- Un CTE estándar no hace referencia a ni define otro CTE en su definición.
- Una definición de CTE anidada incluye definir otro CTE.
- La definición de un CTE secuencial puede hacer referencia a un CTE existente, pero no puede definir otro CTE.
- Un CTE recursivo hace referencia a sí mismo en su definición.
Fabric Warehouse y el punto de conexión de SQL Analytics admiten CTE estándar, secuenciales y anidados (versión preliminar). Aunque los CTE estándar y secuenciales están disponibles con carácter general en Microsoft Fabric, los CTE anidados son actualmente una característica en versión preliminar.
Para obtener más información sobre las expresiones de tabla comunes, vea WITH common_table_expression (Transact-SQL).
Nota:
Durante la versión preliminar, la creación de CTE anidada solo es compatible con SQL Server Management Studio (SSMS). IntelliSense en SSMS no reconoce la sintaxis CTE anidada, pero esto no bloquea la creación de CTE anidado. Para obtener la mejor experiencia, limite los niveles de anidamiento a 64.
Sintaxis
WITH <NESTED_CTE_NAME_LEVEL1> [ (column_name , ...) ] AS
(WITH <NESTED_CTE_NAME_LEVEL2> [ (column_name , ...) ] AS
(
...
WITH <NESTED_CTE_NAME_LEVELn-1> [ ( column_name , ...) ] AS
(
WITH <NESTED_CTE_NAME_LEVELn> [ ( column_name , ...) ] AS
(
Standard_CTE_query_definition
)
<SELECT statement> -- Data source must include NESTED_CTE_NAME_LEVELn
)
<SELECT statement> -- Data source must include NESTED_CTE_NAME_LEVELn-1
...
)
<SELECT statement> -- Data source must include NESTED_CTE_NAME_LEVEL2
)
Instrucciones para crear y usar un CTE anidado
Además de las instrucciones para crear y usar CTE estándar, estas son instrucciones adicionales para las CTE anidadas:
- Un CTE anidado solo se puede usar en una instrucción SELECT. No se puede usar en instrucciones UPDATE, INSERT o DELETE.
- No se permite ninguna instrucción UPDATE, INSERT o DELETE en la definición de un CTE anidado.
- No se pueden duplicar los nombres de CTE en el mismo nivel de anidamiento.
- Un CTE anidado solo es visible para los CTE anidados o CTE secuenciales que están en su nivel superior inmediato.
- Las consultas entre bases de datos se permiten en una definición de CTE anidada.
- Las sugerencias de consulta (es decir, la cláusula OPTION) no se permiten en la definición de un CTE anidado.
- El CTE anidado no se puede usar en CREATE VIEW.
- AS OF no se admite en la definición de un CTE anidado.
- Los CTE anidados se admiten en una definición de subconsulta CTE, pero no en una subconsulta general.
Ejemplos
Diferencias entre los CTE estándar, secuencial y anidados
-- Standard CTE
;WITH Standard_CTE AS (
SELECT * FROM T1
)
SELECT * FROM Standard_CTE;
-- Sequential CTE
;WITH CTE1 AS (
SELECT * FROM T1
),
CTE2 AS (SELECT * FROM CTE1),
CTE3 AS (SELECT * FROM CTE2)
SELECT * FROM CTE3
-- Nested CTE
;WITH OUTER_CTE AS (
WITH INNER_CTE AS (
SELECT * FROM T1
)
SELECT * FROM INNER_CTE
)
SELECT * FROM OUTER_CTE;
El ámbito de nombre de CTE está restringido a su ámbito
Los nombres de CTE se pueden reutilizar en diferentes niveles de anidamiento. No se pueden duplicar los nombres de CTE en el mismo nivel de anidamiento. En este ejemplo, el nombre cte1 se usa tanto en ámbito externo como interno.
;WITH
cte1 AS (
WITH
inner_cte1_1 AS (
SELECT * FROM NestedCTE_t1 WHERE c1 = 1
),
inner_cte1_2 AS (
SELECT * FROM inner_cte1_1 WHERE c2 = 1
)
SELECT * FROM inner_cte1_2
),
cte2 AS (
WITH
cte1 AS (
SELECT * FROM NestedCTE_t1 WHERE c3 = 1
),
inner_cte2_2 AS (
SELECT * FROM cte1 WHERE c4 = 1
)
SELECT * FROM inner_cte2_2
)
CTE anidado complejo con unión, unión a todos, intersección y excepto
CREATE TABLE NestedCTE_t1 (
c1 INT,
c2 INT,
c3 INT
);
GO
INSERT INTO NestedCTE_t1
VALUES (1, 1, 1);
INSERT INTO NestedCTE_t1
VALUES (2, 2, 2);
INSERT INTO NestedCTE_t1
VALUES (3, 3, 3);
GO
WITH
outermost_cte_1 AS (
WITH
outer_cte_1 AS (
WITH
inner_cte1_1 AS (
SELECT * FROM NestedCTE_t1 WHERE c1 = 1
),
inner_cte1_2 AS (
SELECT * FROM inner_cte1_1
UNION SELECT * FROM inner_cte1_1
)
SELECT * FROM inner_cte1_1
UNION ALL SELECT * FROM inner_cte1_2
),
outer_cte_2 AS (
WITH
inner_cte2_1 AS (
SELECT * FROM NestedCTE_t1 WHERE c2 = 1
EXCEPT SELECT * FROM outer_cte_1
),
inner_cte2_2 AS (
SELECT * FROM NestedCTE_t1 WHERE c3 = 1
UNION SELECT * FROM inner_cte2_1
)
SELECT * FROM inner_cte2_1
UNION ALL SELECT * FROM outer_cte_1
)
SELECT * FROM outer_cte_1
INTERSECT SELECT * FROM outer_cte_2
),
outermost_cte_2 AS (
SELECT * FROM outermost_cte_1
UNION SELECT * FROM outermost_cte_1
)
SELECT * FROM outermost_cte_1
UNION ALL SELECT * FROM outermost_cte_2;
CTE anidado se admite en la definición de subconsulta de CTE, pero no en subconsulta general
Esta consulta produce el siguiente error: Msg 156, Level 15, State 1, Line 3. Incorrect syntax near the keyword 'WITH'.
SELECT * FROM
(
WITH
inner_cte1_1 AS (SELECT * FROM NestedCTE_t1 WHERE c1 = 1),
inner_cte1_2 AS (SELECT * FROM inner_cte1_1)
SELECT * FROM inner_cte1_2
) AS subq1;
Las referencias a un CTE no pueden superar su ámbito
Esta consulta produce el siguiente error: Msg 208, Level 16, State 1, Line 1. Invalid object name 'inner_cte1_1'.
;WITH
outer_cte_1 AS (
WITH
inner_cte1_1 AS (
SELECT * FROM NestedCTE_t1 WHERE c1 = 1
),
inner_cte1_2 AS (
SELECT * FROM inner_cte1_1 WHERE c2 = 1
)
SELECT * FROM inner_cte1_2
),
outer_cte_2 AS (
WITH inner_cte2_1 AS (
SELECT * FROM NestedCTE_t1 WHERE c3 = 1
)
SELECT
tmp2.*
FROM
inner_cte1_1 AS tmp1,
inner_cte2_1 AS tmp2
WHERE
tmp1.c4 = tmp2.c4
)
SELECT * FROM outer_cte_2;