패브릭 데이터 웨어하우징의 중첩된 CTE(공통 테이블 식)(Transact-SQL)

적용 대상: Microsoft Fabric의 SQL 분석 엔드포인트 및 웨어하우스

일반적인 테이블 식(CTE)은 일반적으로 복잡한 쿼리를 재사용 가능한 블록으로 분해하여 복잡한 쿼리를 간소화할 수 있습니다.

표준, 순차적, 재귀적 CTE 및 중첩된 CTE를 포함하여 4가지 유형의 CTE가 있습니다.

• 표준 CTE는 해당 정의에서 다른 CTE를 참조하거나 정의하지 않습니다.
• 중첩된 CTE의 정의에는 다른 CTE 정의가 포함됩니다.
• 순차 CTE의 정의는 기존 CTE를 참조할 수 있지만 다른 CTE를 정의할 수는 없습니다.
• 재귀 CTE는 해당 정의에서 자신을 참조합니다.

패브릭 웨어하우스 및 SQL 분석 엔드포인트는 모두 표준, 순차 및 중첩된 CTE(미리 보기)를 지원합니다. 표준 및 순차 CTE는 일반적으로 Microsoft Fabric에서 사용할 수 있지만 중첩된 CTE는 현재 미리 보기 기능입니다.

일반적인 테이블 식 에 대한 자세한 내용은 WITH common_table_expression(Transact-SQL)를 참조하세요.

구문

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
    )

중첩된 CTE 만들기 및 사용에 대한 지침

표준 CTE를 만들고 사용하기 위한 지침 외에도 중첩된 CTE에 대한 추가 지침은 다음과 같습니다.

• 중첩된 CTE는 SELECT 문에서만 사용할 수 있습니다. UPDATE, INSERT 또는 DELETE 문에는 사용할 수 없습니다.
• 중첩된 CTE의 정의에는 UPDATE, INSERT 또는 DELETE 문이 허용되지 않습니다.
• 동일한 중첩 수준의 CTE 이름은 복제할 수 없습니다.
• 중첩된 CTE는 즉시 상위 수준에 있는 중첩된 CTE 또는 순차 CTE에만 표시됩니다.
• 데이터베이스 간 쿼리는 중첩된 CTE 정의에서 허용됩니다.
• 쿼리 힌트(예: OPTION 절)는 중첩된 CTE의 정의에서 허용되지 않습니다.
• 중첩된 CTE는 CREATE VIEW에서 사용할 수 없습니다.
• AS OF은 중첩된 CTE의 정의에서 지원되지 않습니다.
• 중첩된 CTE는 CTE 하위 쿼리 정의에서 지원되지만 일반 하위 쿼리에서는 지원되지 않습니다.

예제

표준, 순차 및 중첩된 CTE 간의 차이점

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

CTE의 이름 범위는 해당 범위로 제한됩니다.

CTE 이름은 다른 중첩 수준에서 다시 사용할 수 있습니다. 동일한 중첩 수준의 CTE 이름은 복제할 수 없습니다. 이 예제에서는 이름이 cte1 외부 범위와 내부 범위 모두에서 사용됩니다.

;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

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는 CTE 하위 쿼리 정의에서 지원되지만 일반 하위 쿼리에서는 지원되지 않습니다.

이 쿼리는 다음 오류와 함께 실패합니다. 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;

CTE에 대한 참조는 해당 범위를 초과할 수 없습니다.

이 쿼리는 다음 오류와 함께 실패합니다. 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;

관련 콘텐츠

• WITH common_table_expression(Transact-SQL)
• SELECT (Transact-SQL)
• Microsoft Fabric의 T-SQL 노출 영역