Grokによる解説記事
SQLのサブクエリとWITH構文（CTE）の解説
SQLで複雑なデータ操作を行う際、サブクエリとWITH構文（Common Table Expression、CTE）は非常に役立つツールです。どちらもクエリ内でデータを一時的に処理するために使用されますが、用途や特徴が異なります。この記事では、まずサブクエリについて詳しく解説し、次にWITH構文を紹介し、それぞれの特徴、使い方、メリット・デメリットをわかりやすく説明します。
サブクエリとは
概要
サブクエリ（副問い合わせ）は、SQLクエリ内にネストされた別のSELECT文です。メインクエリの結果を動的に生成したり、条件を絞り込むために使用されます。サブクエリは()で囲まれ、WHERE句、SELECT句、FROM句など、さまざまな場所で活用できます。サブクエリは単一の値、複数の行、または複数の列を返すことが可能です。
構文
sql
SELECT column1
FROM table_name
WHERE column2 = (
SELECT column3
FROM another_table
WHERE condition
);
サブクエリはメインクエリの一部として実行され、その結果がメインクエリの処理に使用されます。
スカラーサブクエリ：単一の値を返す（例：WHERE句で比較に使用）。
行サブクエリ：複数行を返す（例：INやANYを使用）。
相関サブクエリ：メインクエリの各行と関連して評価される（例：EXISTS句）。
使用例
エディンバラのバス路線データベース（仮想的）で、特定の停留所（例：'Craiglockhart'）を通るバスの番号を検索する例：
sql
SELECT num, company
FROM route
WHERE stop = (
SELECT id
FROM stops
WHERE name = 'Craiglockhart'
);
ここでは、stopsテーブルからCraiglockhartのIDを取得するサブクエリが、メインクエリのWHERE条件で使用されています。
特徴
柔軟性：WHERE、SELECT、FROMなど、さまざまな場所で使用可能。
単発使用：サブクエリは1回だけ実行され、再利用には同じコードを再度記述する必要がある。
相関サブクエリ：メインクエリの行ごとにサブクエリが評価される（例：EXISTSや比較演算子を使用）。
メリット
簡潔さ：単純な条件や一時的な計算を短く記述できる。
即席使用：複雑なロジックを追加せずに、すぐに結果を条件や値として利用可能。
広範な適用性：SELECT、WHERE、FROMなど、SQLの多くの部分で使える。
デメリット
可読性：ネストが深くなるとクエリが複雑で読みづらくなる。
再利用性：同じサブクエリを複数回使用する場合、毎回記述する必要があり、コードが冗長になる。
パフォーマンス：相関サブクエリは各行ごとに実行されるため、データ量が多いと処理が遅くなる可能性がある。
使用場面
単純なフィルタリング（例：特定の値を取得して比較）。
一時的な結果をメインクエリに直接組み込む場合。
複雑な結合を避けたい場合（ただし、パフォーマンスに注意）。
WITH構文（Common Table Expression, CTE）とは
概要
WITH構文は、クエリ内で一時的な結果セット（仮テーブル）を定義し、それを後続のクエリで再利用する仕組みです。Common Table Expression（CTE）と呼ばれ、複雑なクエリを構造化し、可読性を向上させるために使用されます。特に、再帰処理や複数ステップのクエリで威力を発揮します。
構文
sql
WITH cte_name AS (
SELECT column1, column2
FROM table_name
WHERE condition
)
SELECT *
FROM cte_name
WHERE condition;
cte_name：CTEに付ける任意の名前。
複数のCTEを定義する場合は、カンマで区切る（例：WITH cte1 AS (...), cte2 AS (...)）。
CTEはメインクエリや他のCTEから参照可能。
使用例
エディンバラのバス路線で、クレイグロックハートからロチェンドまで2つのバスで移動するルートを検索する例：
sql
WITH bus1 AS (
SELECT start1.num, start1.company, stop1.stop
FROM route AS start1
JOIN route AS stop1
ON start1.num = stop1.num AND start1.company = stop1.company
AND start1.stop != stop1.stop
WHERE start1.stop = (SELECT id FROM stops WHERE name = 'Craiglockhart')
),
bus2 AS (
SELECT start2.num, start2.company, start2.stop
FROM route AS start2
JOIN route AS stop2
ON start2.num = stop2.num AND start2.company = stop2.company
AND start2.stop != stop2.stop
WHERE stop2.stop = (SELECT id FROM stops WHERE name = 'Lochend')
)
SELECT bus1.num, bus1.company, stops.name, bus2.num, bus2.company
FROM bus1
JOIN bus2 ON bus1.stop = bus2.stop
JOIN stops ON bus1.stop = stops.id
ORDER BY bus1.num, bus1.company, stops.name, bus2.num, bus2.company;
この例では、bus1とbus2という2つのCTEを定義し、乗り換え可能な停留所を特定しています。
特徴
一時的な結果セット：クエリ実行中だけ存在する仮想テーブル。
再利用性：同じCTEをクエリ内で複数回参照可能。
再帰処理：WITH RECURSIVEを使用すると、階層データやツリー構造を処理できる。
可読性：複雑なロジックを名前付きのブロックに分割。
メリット
可読性：サブクエリを名前付きで整理し、複雑なクエリを読みやすくする。
再利用性：同一クエリ内で同じ結果セットを複数回参照可能。
再帰処理：階層データ（例：組織図や親子関係）の処理に適している。
デバッグ：CTEごとに結果を確認でき、トラブルシューティングが容易。
デメリット
パフォーマンス：一部のDBMS（例：PostgreSQL）では、CTEが「最適化フェンス」として扱われ、結果が一時的にメモリに保存されるため、大きなデータセットでオーバーヘッドが発生する場合がある。
スコープ：CTEは定義されたクエリ内でのみ有効で、別のクエリで再利用できない。
使用場面
複雑なクエリをステップごとに分割したい場合。
同じ結果セットを複数回参照する必要がある場合。
再帰的なデータ処理（例：ツリー構造やグラフ）。
可読性や保守性を重視する場合。