SQLのWITH句の使い方や感想

WITH句を使うと見やすくなるとなんかで聞いたので使いました。
使い方数点共有や感想述べます。

WITH句とは

副問い合わせ（サブクエリ）に名前を付けれる機能。
一時テーブルみたいなもの。

With句構成例

-- WITH句
WITH test AS (
SELECT * FROM テーブル名 WHERE 条件
)

-- WITH句テーブル検索
SELECT * FROM test;

WITH句内のSQLが先に実行される。

どんな使い方すればよいの？

数点思いついたり目にした。

例①：抽出項目ごとにWITH句作成し最後にJOIN
例②：複雑なUPDATEしたいとき処理単位ごとにWITH句作成しUPDATE
例③：複雑な検索結果ごとにWITH句作成し最後にJOIN等
例④：さわりたくない複雑なSQLにちょっと条件加えたい、カウントしたい

例①：抽出項目ごとにWITH句作成し最後にJOIN

例：「氏名」「社員番号」「メールアドレス」「ID有効期限」を抽出

-- 「氏名」「社員番号」抽出
WITH 社員情報 AS (
SELECT ID,氏名,社員番号 FROM 社員情報テーブル WHERE なんか条件
)

-- 「メールアドレス」抽出
, メールアドレス AS (
SELECT ID,メールアドレス FROM 属性テーブル 
WHERE 属性名 = 'メールアドレス'
)

-- 「ID有効期限」抽出
, ID有効期限 AS (
SELECT ID,ID有効期限 FROM 属性テーブル 
WHERE 属性名 = 'ID有効期限'
)

-- WITH句JOINしてSELECT
SELECT 
  社員情報.氏名
  , 社員情報.社員番号
  , メールアドレス.メールアドレス
  , ID有効期限.ID有効期限
FROM 
  社員情報
  JOIN メールアドレス
    ON 社員情報.ID = メールアドレス.ID
  JOIN ID有効期限
    ON 社員情報.ID = ID有効期限.ID
;

上記くらいの抽出であればわざわざWITH句使わなくてもよいと思うが、
例えば抽出項目が増えて、たくさんJOINやJOINの際ANDしないといけなくなった場合、
とても見づらいSQLになってしまうのでそんな場合に有効。

例②：複雑なUPDATEしたいとき処理単位ごとにWITH句作成しUPDATE

例：ポスグレ配列内のID有効期限の値が残り10日以内のものだけ有効期限を30日延長

-- ID有効期限の所持例：{20200507.20201010,20200513}
-- 処理対象求め → 配列展開 → 対象30日延長した値表示 → 再配列化 → UPDATEでいけるはず

WITH 処理対象ID AS (
-- 処理対象IDと有効期限配列をSELECT
)

, 処理対象の有効期限の配列展開 AS (
-- 頑張ってunnestとwith ordinalityを使い配列展開
FROM 
  処理対象ID 
)

, 有効期限が残り10日以内のもの30日延長 AS (
-- 頑張ってCASE文とか使い、
-- 残り10日以内の有効期限の表示を30日延長した値を表示するSELECT
FROM 
  処理対象の有効期限の配列展開
WHERE 
  ${有効期限が残り10日以内のものだけ}
)

, 有効期限を再度配列化 AS (
-- 配列再度配列化するSELECT
FROM 
  有効期限が残り10日以内のもの30日延長
)

-- 最後のWITH句を使ってUPDATE
UPDATE
  属性テーブル
SET
  有効期限 = 更新後有効期限
FROM 
  有効期限を再度配列化
WHERE
  ${「有効期限を再度配列化」に存在するID}
;

上記を一つのUPDATE文だけで表現するとなかなかの見づらさだと思うが、
処理単位ごとに分ければまだ見やすいはず。。。行数長くなるが。。。

また、分けたほうがデバッグもしやすい。
バグってそうなWITH句直後にSELECT文入れれば途中経過SELECTできる。

例：デバッグ

~~~ いろんな処理  ~~~

, バグってそうなWITH句 AS (
-- バグってそうなSELECT
)

-- デバッグ!!!!!
SELECT * FROM バグってそうなWITH句;

例③：複雑な検索結果ごとにWITH句作成し最後にJOIN等

例：2つのテーブルのそれぞれ条件にあったIDを抽出したい

WITH 複雑な検索結果１ AS (
SELECT ID FROM 社員情報テーブル WHERE ${なんか条件}
)

, 複雑な検索結果２ AS (
SELECT ID FROM 属性テーブル WHERE ${なんか条件}
)

-- 複雑な検索結果１２両方存在するIDを求めたい
SELECT 
  複雑な検索結果１.ID
FROM 
  複雑な検索結果１
  , 複雑な検索結果２
WHERE
  複雑な検索結果１.ID IN ( 複雑な検索結果２.ID )
;

上記のような使い方してる方もいた。

例④：さわりたくない複雑なSQLにちょっと条件加えたい、カウントしたい

例：さわりたくないSQL結果をカウントしたい

WITH さわりたくないSQL AS (
-- さわりたくないSQL
)

-- カウントしたりちょっと条件加える
SELECT 
  count(*)
FROM 
  さわりたくないSQL
WHERE
  ${ちょっと加えたい条件}
;

上記は案外使う場面あった。
ただカウントしたいだけなのに数行なおさなくてよかったり、
たくさんJOINしててどこに条件加えればよいかパッと見づらい場合とか。

感想（メリットやデメリット）

メリット

①見やすい
プロジェクト特有のデータ持ち方見慣れていない人には助かる気がする。
1個所にたくさんJOINされているSQLやっぱ見づらい。

デメリット

①行数が長くなりがち
プロジェクト特有のデータ持ち方マスターしている人にとっては1個所にたくさんJOIN集まってる方が見やすいかも？

②WITH句内の検索結果多いとパフォーマンスに影響
WITH句検索結果はメモリ上に保存されるので、検索結果を多くなりすぎないよう工夫が必要な場合があるかも。