WITH句の使い方

　以前、階層問合せの記事で使ったwith句の使い方を改めてまとめてみました。さらにテーブル結合の記事で書いた内容でも使えそうなのです。

with句の書き方

　まずは、試しにwith句を書いてみました。複数のwith句を書く場合は、カンマで繋げます。

1. master1から複数条件で対象となる候補データだけ抽出し、temporary1に格納します。
2. master2から複数条件で対象となる候補データだけ抽出し、temporary2に格納します。
3. table1をもとにして、temporary1、temporary2から内容に合致するデータを抽出します。

WITH
 temporary1 AS (
    SELECT
     id
    ,name
     FROM
     master1
     WHERE
     class1 = 'A'
     AND
     class2 = 1
)
,temporary2 AS (
    SELECT
     id
    ,name
     FROM
     master2
     WHERE
     class1 = 'B'
     AND
     class2 = 0
)
SELECT
 table1.id
,table1.name
,temporary1.name
,temporary2.name
 FROM
 table1
 LEFT JOIN temporary1 ON table1.id = temporary1.id
 LEFT JOIN temporary2 ON table1.id = temporary2.id
 WHERE
 table1.class = 'Sample'

　まず、with句の中にあるＳＱＬ文が実行されるので、temporary1、そしてtemporary2の中にある抽出ＳＱＬが実行され、temporary1、temporary2が生成された後に、最後のselect文が実行されるという順番で動きます。

　うまく使えば、テーブル結合で発生してしまいそうな複雑な結合条件を、読みやすく書けそうです。試しに、テーブル結合で書き直してみました。

SELECT
 table1.id
,table1.name
,temporary1.name
,temporary2.name
 FROM
 table1
 LEFT JOIN temporary1 ON (
    table1.id = temporary1.id
    AND
    temporary1.class1 = 'A'
    AND
    temporary1.class2 = 1)
 LEFT JOIN temporary2 ON (
    table1.id = temporary2.id
    AND
    temporary2.class1 = 'B'
    AND
    temporary2.class2 = 0)
 WHERE
 table1.class = 'Sample'

　この程度の抽出条件なら、どちらで書いても問題ないような気がしてしまいます。どちらかというとテーブル結合のほうがすっきりしているように見えてくる・・・と思うのは、記述量の多さの違いからでしょう。

順序良く実行されるならば・・・

　with句単位で抽出ＳＱＬ文が書けるようになることで、実行テストをする際に、一度に複雑なテーブル結合をさせる場合よりも、各段階を追ってテストができるようになり、異常部分を追いかけやすくなります。

　しかし、with句で実行されたtemporary1やtemporary2は、メモリー上に存在しますので、あまりにも大きな抽出結果をもたらすwith句を実行すると、処理速度が著しく低下する恐れがありますので、ご注意ください。この辺りはバランス感覚で対処しましょう。

with句への期待

　古くからあるシステムの中には、複数の一時テーブルを作って、抽出処理を分散させて処理しているものがあります。
　そういったシステムが、時代の流れに沿って新しい環境への移植作業が必要になった場合、with句を使えば、あまり悩まずに、移植することができそうな予感がします。
　・・・そうだったら良いなぁという希望ですが。

• 技術手帳
• ＳＱＬ