共通テーブル式 Common Table Expression （CTE) を利用する

ポクルー

質問: SQL中のWITH

SQL文内に WITH [テーブル名] AS( SELECT ....) という記述があるのですが、これは何でしょう？一時テーブルっぽく見えます。

共通テーブル式 Common Table Expression（CTE)について紹介します。

共通テーブル式 - Common Table Expression（CTE)

WITH ～ AS (...) という形で定義される、一時的な名前付きのクエリ結果です。 見た目は一時テーブルに似ていますが、実際にはメモリやtempdbに物理的なテーブルを作成するわけではありません。

CTEの特徴

書式

WITH [CTE名] AS (
 [クエリ]
)
[クエリ]

例

INSERT INTO products (id, model, name, category, price) VALUES
(1, 'C-XM01', 'モーダンチェア', 'チェア', 56000),
(2, 'X-XD05', 'ラージデスク', 'テーブル', 87000),
(3, 'A-DA40', 'ラウンドダイニングチェア', 'チェア', 28000),
(4, 'O-XX100', 'ナチュラルオフィス', 'チェア', 13800),
(5, 'R-D400', 'ラウンドダイニングテーブル', 'テーブル', 128000),
(6, 'R7000', 'ウッドキャビネット', 'その他', 32000);

上記のテーブルでpriceの平均値より高いpriceの値を持つレコードを列挙する場合の例です。

今回はサブクエリではなく、CTEを利用して実行します。以下のSQLを記述します。
WHERE句でAVG(price)を記述するとエラーになるため、先にpriceの平均値を計算して、CTEに挿入しておき、後半のクエリでCROSS JOINして priceの平均値を参照する動作になります。

WITH Average AS (
SELECT AVG(price) as ap FROM products
)
SELECT productsBV.* FROM products CROSS JOIN Average WHERE price > Average.ap

select id, model, name, category, price FROM products WHERE price > AVG(price)

実行結果

以下の結果となります。

例2: 重複するレコードの削除

こちらの記事を参照してください。

著者

neg

iPentec.com の代表。ハードウェア、サーバー投資、管理などを担当。
Office 365やデータベースの記事なども担当。