SQL Server - WITH

概要

WITH句があるSQL文は、その後に記述されているSELECT文よりも先に実行される。
WITH句は、共通テーブル式またはCTE(Common Table Expression)とも呼ばれる。

WITH句のメリットは、FROM句の箇所にあるSQL文が外に出るため、可読性が上がることである。

※注意
WITH句は、MySQLの古いバージョンでは未対応である。

以下の例は、WITH句を使用している。

 WITH <変数1> as
 (
    SELECT <カラム名> FROM <テーブル名>
    WHERE  <条件>
 )
 SELECT <カラム名>
 FROM <テーブル名>, <変数1>
 WHERE <条件>;

WITH句の後にある<変数1>は、SQL文で使用する変数である。
その後の括弧内にSQL文を記述する。

2つ目のFROM句の箇所に、WITH句の後にある<変数1>を指定している。

これにより、WITH句の後にあるSQL文をテーブル(ビュー)のように使用できる。
これは、FROM句の箇所に記述する副問い合わせと同様の意味となる。

WITH句を使用する場合

下表に示すテーブルがある。

T_Sampleテーブル

Name	Date	Sales
A	20210101	200
B	20210102	150
C	20210103	100

以下の例では、WITH句を使用してデータを抽出している。

 WITH T_Test as
 (
    SELECT AVG(Sales) AS SalesAverage FROM T_Sample
 )
 SELECT b.Name, b.Date, b.Sales
 FROM T_Test a, T_Sample b
 WHERE a.SalesAverage < b.Sales;

1行目は、WITH句の後のT_Testは、AS句の後にあるSQL文の名前である。
6行目は、1行目で定義したT_Testを、テーブル(ビュー)のように使用している。

結果は、以下の通りである。

Name     Date     Sales
A        20210101 200

WITH句の値が複数の場合

WITH句の値が複数でも指定できる。
以下の例では、WITH句で複数の値を取得した後、8行目のIN句の条件にしている。

 WITH T_Test as
 (
    SELECT Sales FROM T_Sample
    WHERE Sales > 100
 )
 SELECT b.Name, b.Date, b.Sales
 FROM T_Test a, T_Sample b
 WHERE b.Sales in (a.Sales);

結果は、以下の通りである。

Name     Date     Sales
A        20210101 200
B        20210102 150

WITH句を使用しない場合

下表に示すテーブルがある。

T_Sampleテーブル

Name	Date	Sales
A	20210101	200
B	20210102	150
C	20210103	100

FROM句の箇所にある副問い合わせを使用して、レコードを抽出する。

 SELECT b.Name, b.Date, b.Sales
 FROM
 (
    SELECT AVG(Sales) AS SalesAverage FROM T_Sample
 ) a, T_Sample b
 WHERE a.SalesAverage < b.Sales;

4行目は、Salesカラムの平均を求める副問い合せであり、副問い合わせの結果をテーブル(ビュー)のように使用している。
1つ目のFROM句の中に、SQL文が記述されているため、WITH句より可読性が落ちている。

結果は、以下の通りである。

Name     Date     Sales
A        20210101 200