intersect, and except

CTE names can be reused at different nesting levels. CTE names at the same nesting level can’t

be duplicated. In this example, the name

is used in both outer and inner scope.

cte1

)
SELECT
*
FROM
INNER_CTE
)
SELECT
*
FROM
OUTER_CTE;

;
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
)

CREATE
TABLE
NestedCTE_t1 (
c1
INT
,
c2
INT
,
c3
INT
);
GO
INSERT
INTO
NestedCTE_t1