SQLのCTEを使ってみましょう！

こんにちは！ベトナムからリモート中のエンジニア、コアです。

複雑のSQLクエリを書く際に、おそらく皆さんが一時テーブルを使用したことがありますよね。
今回、一時テーブルと似ているCTEを紹介したいと思います。

はじめに

CTEとは「Common Table Expression」（共通テーブル式）の略語です。
CTEはWITH句で定義された名前付きサブクエリです。CTEは単純なクエリから派生し、単一のSELECT、INSERT、UPDATE、DELETE、MERGEステートメントの実行スコープ内で定義されます。
CTEは、モジュール性を高め、メンテナンスを簡素化します。

構文

WITH cte_name [(col1,...n)]
AS
(
  SELECT col1,...n
  FROM someTable
  ..........
)
SELECT * FROM cte_name

CTE使用について

シングルCTEを使用します。
例）

WITH totalOrders
AS
(
  SELECT  user_id, SUM(order_price_total) as totalAmount
    FROM  w2_Order
   WHERE  order_date = @today AND order_status = 'ORD'
  GROUP BY user_id
)

SELECT  w2_User.user_id, name, mail_addr, totalAmount
  FROM  w2_User
        INNER JOIN totalOrders
        ON (w2_User.user_id = totalOrders.user_id)
 WHERE  totalAmount > 10000

複数のCTEが使用できます。
例）

WITH totalOrders
AS
(
  SELECT  user_id, SUM(order_price_total) as totalAmount
    FROM  w2_Order
   WHERE  order_date = @today AND order_status = 'ORD'
  GROUP BY user_id
), countOrders
AS
(
  SELECT  user_id, count(*) as orderCount
    FROM  w2_Order
   WHERE  order_date = @today AND order_status = 'ORD'
  GROUP BY user_id
)

SELECT  w2_User.user_id, name, mail_addr, totalAmount
  FROM  w2_User
        INNER JOIN totalOrders
        ON (w2_User.user_id = totalOrders.user_id)
        INNER JOIN countOrders
        ON (w2_User.user_id = countOrders.user_id)
 WHERE  totalAmount > 10000 AND orderCount < 3

CTEと一時テーブルとの比較

CTEと一時テーブルでは異なる部分がかなり多いですが、自分の知っている限りその違いを記述させていただきます！

■CTEについて
・TempDBに保存されない
・インデックス付けができない
・制約を持つことはできない
・本質的に使い捨てビューである
・次のクエリが実行されるまで持続する

■一時テーブルについて
・TempDBに保存される
・インデックス作成できる
・制約を持つことはできる
・他のクエリまたはサブプロシージャで参照できる
・現在の接続の存続期間中持続する

まとめ

いかがでしょうか。
SQLクエリ作成の意図によって、一時テーブルか、CTEかを適当に使ってみてくださいね。