WITH句を使用してテーブルを作成せずにSQLの動作を確認する

状況

・SQL(SELECT文)の動作検証したい。
・SELECT文の動きを実際に見せて後輩や部下にSQLを教えたい。
・けど、実際にテーブルを作成してデータもインサートするのはするのはめんどくさい。。。

みたいな状況ってないでしょうか。私は結構よくあります。
こんな時、WITH句を使用すると便利です。

そもそもWITH句って何？

WITH句を知らない、あるいは知っているけどほとんど使用したことがない、という人向けに簡単に解説。
一言でいうと、SELECT文の結果に名前を付けることでSQL文の記述を簡単にすることができる構文。

構文

with <名前> as (select文)

例

with tableC as (
    select * 
    from tableA A
    inner join tableB B
    on A.colA = B.colA
)
select colA, sum(bolB)
from tableC
group by colA

使い方はこんな感じ。
tableAとtableBをjoinした結果にtableCという名前を付けることで、
WITH句に続くSQLで、tableCを実際のテーブルっぽく扱うことができる。

WITH句でテーブルを作らずにSELECT文の動作を検証

いよいよ本題。
通常SELECT文の動作を検証しようと思うと、create tableを使ってテーブルを作成し、inesrtで必要なデータを入れる必要がある。
しかし、SELECT文の動きを検証したいだけの時にわざわざテーブル作るのは面倒。
削除し忘れて後で上司に怒られたりするのも嫌。
そこでWITH句を使い、中のSELECT文でunionを使用することで、疑似的なテーブルを作成する。
それを本物のテーブルっぽく扱うことで、SELECT文の動作検証ができる。

with users as (
    select 1 user_id, '佐藤' user_name, 20 age, '男' sex
    union
    select 2, '鈴木', 24, '女'
    union
    select 3, '田中', 22, '女'
    union
    select 4, '山本', 31, '男'
    union
    select 5, '高橋', 28, '男'
)
select sex, max(age) max_age
from users
group by sex;

結果

sex	max_age
男	31
女	24

こんな感じで、実際にはusersというテーブルは作成していなくても、
あたかもそういうテーブルが存在するような感じでselect文の動作を確認することができる。

補足

・上の例はPostgreSQLで検証してます。
・Oracleの場合、ダミーレコードを作成する場合は from dual が必要。
・unionのところはunion allでも可。件数多いときはunion allの方がいいかも。
・あくまでSELECT文の結果を確認するためのテクニック。
速度とか実行計画は一切あてにならないので、
速度まで含めて検証したい場合はきちんとテーブルとインデックスを作りましょう。