WITH句はサブクエリと非常によく似ています(捉え方によってはサブクエリの一種でもあります)が、このWITH句を使いこなせるようになることで、サブクエリでは実現できなかったことが可能となります。
例えば結合テーブルを何度も使いたい、でもクエリは出来るだけ短くしたいというようなケースでは、WITH句が本領を発揮します。
今回はそんなWITH句について出来るだけわかりやすく解説してみたいと思います。
SQLでクエリが冗長になってしまっている方などは必見の内容となっていますので、是非一読してみてください！
サブクエリとWITH句の違い
そもそもWITH句はサブクエリと同一視される方とそうでない方に二分します。
WITH句は厳密にはサブクエリではないのですが、同一視される方の多くは「性質が極めて似ているため」そのような認識となっている場合が多くあります。
サブクエリとは？
簡単に言い換えるなら“クエリの中に入れ子として別のクエリを記述する”ことを指します。
最もわかりやすい例としては「SELECTの結果で検索をかける」クエリでしょう。
サブクエリを使用することでSELECTやINSERT/UPDATE、DELETEの幅が広がるため、非常に重宝します。
WITH句とは？
WITH句を使うと、該当クエリ内で新たに参照テーブルを作り、その参照テーブルを基にしたクエリを記述することが出来ます。
クエリの中で更に何か別の作業が出来るという部分だけで見るとサブクエリと同じことから、性質としては非常によく似ています。
但しサブクエリとの決定的な違いは「WITH句で作成した参照テーブルは同一クエリ内で何度でも使い回せる」という部分です。
WITH句の記述方法
WITH句とサブクエリの違いを学習したところで実際にテーブルを見ながらWITH句の使い方について解説していきたいと思います。
まず最初はWITH句の記述についてです。
WITH句は次のように記述します。
WITH [テーブル名]([カラム名],[カラム名]・・・) AS [テーブル作成のためのクエリ]
記述自体はそう難しいものではありませんが、実際にコードを見た方がわかりやすいため、早速サンプルコードに移ります。
まず次のテーブルを見てください。
// challenger_table
id member_id name age sex mail
----------- ---------- -------------------- ----------- ---------- --------------------------------------------------
1 A1001 Taylor 25 male Alex_Taylor@samplemail.com
2 A1002 Willson 20 male Tomath_Willson@samplemail.com
3 A1003 Lewis 28 female Ashly_Lewis@samplemail.com
4 A1004 Morgan 19 male Mark_Morgan@samplemail.com
5 A1005 Burton 24 female Ayla_Burton@samplemail.com
// try_table
member_id counter score
---------- ----------- -----------
A1001 1 6
A1003 1 5
A1002 1 1
A1001 2 3
A1001 3 1
A1005 1 6
A1004 1 6
A1003 2 4
A1002 2 2
A1003 3 6
A1005 2 2
A1004 2 6
A1002 3 3
A1005 3 5
A1004 3 3
A1001 4 5
A1005 4 6
A1002 4 4
A1001 5 1
A1002 5 2
A1004 4 6
A1003 4 4
A1005 5 1
A1004 5 6
今回のサンプルコードではこの二つのテーブルを使い、「サイコロの出目の合計数が3位までのチャレンジャーのメールアドレスを取得」します。
このケースでは次のように記述していきます。
WITH sample_top3_score(member_id, score) AS ( SELECT member_id, SUM(score) FROM try_table GROUP BY member_id ORDER BY SUM(score) DESC OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY ) SELECT mail FROM challenger_table JOIN sample_top3_score ON challenger_table.member_id = sample_top3_score.member_id ORDER BY score desc ; GO mail -------------------------------------------------- Mark_Morgan@samplemail.com Ayla_Burton@samplemail.com Ashly_Lewis@samplemail.com
まず最初にWITH句を使って参照テーブルを作成(1行目～15行目)している部分ですが、2行目でテーブル名とカラム名を設定します。
3行目～8行目で、どのテーブルのどのフィールドからデータを持ってくるかの設定を行っています。
9行目～15行目ではmember_id毎に集計を行わせるように設定し、そのうち最もscoreの高い3名を抽出するよう設定しています。
更に17行目～は抽出したレコードをJONで結合、challenger_tableからデータを抽出し表示時に再度scoreの高い順に表示させるようにしています。
このように、WITH句を使うことで参照テーブルを作成しそのテーブル名を使ってSQL操作をすることが可能となります。
まとめ
今回はWITH句を使ったレコード抽出について解説してみましたが、いかがでしたか？
慣れていない方には少しややこしいクエリに見えますが、WITH句を使いこなせるようになれば何度も同じクエリを記述することなく、テーブルを再利用することが可能です。
この記事を読みながらWITH句について理解できるよう頑張ってみましょう！
同一クエリ内とは” 記述の始め～ ; (セミコロン) “までを指します。
WITH句で作成したテーブルは一時的な記憶テーブルなので、クエリを終了させてしまうとその時点でWITH句の効力自体も失ってしまいます。
この時点で記憶テーブルも削除されてしまうため注意しましょう。