[Oracle]長文SQLをWITH句を使って、SQLの繰り返しをまとめる

自分、WITH句アレルギーです＼(^o^)／

今でこそ、使うようになりましたが、最初は拒否反応バリバリでした。

だって、WITH句で何ができるのか良く分からなかったからです。

VIEWのように使えるから、使うべき！！と執拗に推してくる人もいましたが、今までは拒否っていました。

というか、自分で作成するSQLでは避けてました。

WITH句の何がいいのか？

簡易のViewとして使えるところっていうのが、現時点で感じているメリットです。

Viewの何がいいのか？

っていうのは、置いておきます(説明も長くなるし)。

ポイントは、「簡易のView」。

これに尽きる。

簡易のViewのメリット

これは、作成しなくていいことに尽きるかと。

作成しなくていいということは、作成できない環境でも使える訳です。

社内でのテスト環境だったり、開発初期とか、ローカル環境でのViewの追加はさほど問題ないんです。

影響は少ないし。

問題が出てくるのは、お客様環境とか、稼動中(稼動間近)のシステムの修正やらが発生したとき。

この状況でViewを追加したいなどと進言してしまうと、非常に面倒くさいことになりますｗ

場合に寄っちゃ、お客様環境での作業のための申請書を何枚も書かなくてはいけなくなったり・・・

そもそも、新規Viewの必要性をプロジェクトリーダーや、その上の人々に説明し、納得してもらわねばならなくなるんですよ。

非常に~~面倒くさい~~時間がかかるんです。

そんな時に、簡易のViewとして使えて、単純に同じテーブルを何個も連結するよりも高速化できるなら、使う価値は十分にある！

実際に使ってみる

今、割と発生するシチュエーションが、複数行のデータを1行で横並びに取得したいっていうパターン。

DAOやらADOやらでデータ取得した後に、複数行が主キー1つ分のデータでまとまっていないと、ループ処理とかしづらいし、余計な時間がかかる。

例えば、同一主キーのデータが10行分取得出来たとすると

1行目のデータが存在しない場合の処理
1行目のデータが○項目目に該当するデータか判定

なんていうループを10行分まわす必要があるわけです。

取得できるデータが10行(=10項目)とは限らないので、何項目目に該当するのかも判断しなきゃいけない。

となると、最初から1行で取得できていることのメリットはデカイ。

table_02のcol003列を1行に表示するために、今まで書いていたSQLは以下。


<pre>SELECT
    tbl_01.id
  , tbl_01.col001
  , tbl_01.col002
  , query1.col003 AS "003_001"
  , query2.col003 AS "003_002"
  , query3.col003 AS "003_003" 
FROM
  localtest.table_01 tbl_01 
  INNER JOIN ( 
    SELECT
        tbl_02.id
      , tbl_02.id_item
      , tbl_02.col003 
    FROM
      table_02 tbl_02 
    WHERE
      tbl_02.id_item = '001'
  ) query1 
    ON tbl_01.id = query1.id 
  INNER JOIN ( 
    SELECT
        tbl_02.id
      , tbl_02.id_item
      , tbl_02.col003 
    FROM
      table_02 tbl_02 
    WHERE
      tbl_02.id_item = '002'
  ) query2 
    ON tbl_01.id = query2.id 
  INNER JOIN ( 
    SELECT
        tbl_02.id
      , tbl_02.id_item
      , tbl_02.col003 
    FROM
      table_02 tbl_02 
    WHERE
      tbl_02.id_item = '003'
  ) query3 
    ON tbl_01.id = query3.id; 
</pre>

これを、WITH句を使用して書くと以下。


<pre>WITH view_tbl_02 AS ( 
  SELECT
      tbl_02.id
    , tbl_02.id_item
    , tbl_02.col003 
  FROM
    table_02 tbl_02
) 
SELECT
    tbl_01.id
  , tbl_01.col001
  , tbl_01.col002
  , query1.col003 AS "003_001"
  , query2.col003 AS "003_002"
  , query3.col003 AS "003_003" 
FROM
  table_01 tbl_01 
  LEFT JOIN ( 
    SELECT
        * 
    FROM
      view_tbl_02 
    WHERE
      view_tbl_02.id_item = '001'
  ) query1 
    ON query1.id = tbl_01.id 
  LEFT JOIN ( 
    SELECT
        * 
    FROM
      view_tbl_02 
    WHERE
      view_tbl_02.id_item = '002'
  ) query2 
    ON query2.id = tbl_01.id 
  LEFT JOIN ( 
    SELECT
        * 
    FROM
      view_tbl_02 
    WHERE
      view_tbl_02.id_item = '003'
  ) query3 
    ON query3.id = tbl_01.id; 
</pre>

SQLの修正が楽になる

WITH句を使ったSQLだと行数的には2行ほど増えてますが、後々の修正のことを考えるとWITH句を使ったほうが修正ミスが減ります。

SQLを見ると分かりますが、LEFT JOIN句以降はWHERE句以外は同じです。

要は、id_itemの数だけ繰り返しが発生します。

10項目分なら、LEFT JOIN以下が10回記述されるわけです。

ここで、修正が必要となり、LEFT JOIN句の取得内容を変更する必要が出てきました。

連結条件が増えたり
絞り込み条件の変更だったり

という例として書こうと思ってたんですが、実際に修正対応が必要になった訳です＼(^o^)／

~~面倒くさい~~時間がかかる・・・

LEFT JOINの中のSQLを全て修正しようとすると、途中で間違える可能性もあるし、修正がもれる可能性もあるんですよ。

(実際にポカして怒られてます・・・)

実際、修正対象のSQLは、見やすい様に整形してはあるものの、1000行超えです。

~~面倒くさい~~時間がかかる・・・

いや、正直に面倒くさいｗ


<pre>WITH view_tbl_02 AS ( 
  SELECT
      tbl_02.id
    , tbl_02.id_item
    , tbl_02.col003 
  FROM
    table_02 tbl_02
) 
</pre>

そこでWITH句を使っていれば、WITH句内の修正で済む場合は、以降のLEFT JOIN句の修正は基本的に不要になります。

もちろん、個別条件が変わったりすれば、LEFT JOIN句を全て修正する必要はあるけど、それでも、大部分を同じに修正する時のコピペ漏れとかの可能性を考えれば修正箇所が少なくなるメリットはデカイ。

デメリットは？

長文のSQLの場合、WITH句で何が定義されているか分かりにくくなりやすいので、あらかじめ確認が必要かな。

直接LEFT JOIN句とかに書いてあれば、そこを見るだけなので、ある意味では分かりやすい。

WITH句を使うべきかどうか？

数百行になるSQLや、条件だけ異なる同じSQLを繰り返す時なんかは、一度検討してみると良い。

後は、同じテーブルをほとんど同じ条件で連結しているような時には、実行時間の高速化になる可能性もある。

特に繰り返しが発生する場合は、一度、WITH句を使えないか試すべき。