理系学生日記

おまえはいつまで学生気分なのか

同じ問合せブロックが複数出現するときは with 句 使えばいいのでは

1 つの SQL に同じ問い合わせブロックが何度も何度も出現するときは、それを出現回数分 DBMS に検索させるのマジでムダだしパフォーマンス悪くなるから、with 文で一時的なビューを作るようにした方がいいです。
以下は、Oracle の公式ドキュメントに記載されてる例ですけど、channel_summary っていうビューを一時的に作ってやって、それを 2 箇所で参照してます。with を使わないと、2 回当該の問い合わせブロックの実行が必要になってしまいますね。
この例みたく集計とか絡むと遅くなりがちだし、sales、channels テーブルがクソみたいに巨大でキャッシュに乗らないと、一々 Disk I/O 発生して遅延デカくなってストレスで死ぬ*1

WITH channel_summary AS (
    SELECT channels.channel_desc, SUM(amount_sold) AS channel_total 
    FROM sales, channels
    WHERE sales.channel_id = channels.channel_id 
    GROUP BY channels.channel_desc
)
SELECT channel_desc, channel_total
FROM channel_summary 
WHERE channel_total > (
    SELECT SUM(channel_total) * 1/3
    FROM channel_summary
)

使いどころとしては上のような場合であったり、いわゆるオンラインで参照するテーブルとその履歴テーブルが分かれていて、両テーブルと集計結果を JOIN したいようなケースだったり。

with v as (
  ...
)
select a, b
from table_online
  inner join v on (table_online.c = v.c)
where
  ...
union all
select a, b
from table_history
  inner join v on (table_history.c = v.c)
where
  ...

*1:キャッシュに乗らないような DB 設計をするなという話はある