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 設計をするなという話はある