Fabric データ ウェアハウスの入れ子になった共通テーブル式 (CTE) (Transact-SQL)
適用対象:Microsoft Fabric 
SQL 分析エンドポイントと Warehouse
共通テーブル式 (CTE) を使用すると、通常複雑なクエリを再利用可能なブロックに分解することで、複雑なクエリを簡略化できます。
CTE には、 standard、 sequential、 recursive、 nested CTE の 4 種類があります。
- 標準 CTE は、その定義内で別の CTE を参照または定義しません。
- 入れ子になった CTE の定義には、別の CTE の定義が含まれます。
- シーケンシャル CTE の定義は既存の CTE を参照できますが、別の CTE を定義することはできません。
- 再帰 CTE は、その定義内でそれ自体を参照します。
Fabric Warehouse と SQL 分析エンドポイントは、どちらも standard、 sequential、および nested CTE (プレビュー) をサポートします。 Microsoft Fabric では標準 CTE とシーケンシャル CTE が一般提供されていますが、入れ子になった CTE は現在プレビュー機能です。
一般的なテーブル式の詳細については、「 WITH common_table_expression (Transact-SQL)」を参照してください。
Note
プレビュー期間中、入れ子になった CTE の作成は SQL Server Management Studio (SSMS) でのみサポートされます。 SSMS の Intellisense では、入れ子になった CTE 構文は認識されませんが、入れ子になった CTE の作成はブロックされません。 最適なエクスペリエンスを得るために、入れ子のレベルを 64 に制限します。
構文
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 では使用できません。
- 入れ子になった CTE の定義では、AS OF はサポートされていません。
- 入れ子になった 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;