SQLZOOというSQLの結果も返してくれる、とても優れたSQLの学習サイトがあるのですが、以前、社内の若手を中心にこのサイトの問題を勧め解いて貰いました。
初歩的なものも多いのですが、SQL入門者でも熟練者の復習としても、とてもよい教材でお勧めです。
最後のSelf join は、単純テーブル構成ながら、幾人かは ここの最終問題辺りで詰まってクリアに至らなかったのですが「Self join」を理解する上でとても秀逸なTutorialだと思います。
ここでは、このSelf join の「＃８」に焦点を当てて「join」の組み立ての違いについて考察してみたいと思います。
Self join のテーブルを参考にさせて頂き、より日本人（特に大阪人）に馴染みやすい以下の表を用意しました。
テーブル構成は見たままですが、t_connectが駅情報と路線情報を紐づけています。言い換えれば、このテーブルが路線に紐づいている駅リストとなります。
Question
それでは、SQLZOOのSelf join「＃８」の類似の問題として「新大阪」から「本町」を結んでいる「路線名」を検索します。但し一本の路線で結ばれているものとします。
大阪人であれば、SQLで求めるまでもなく「OM御堂筋線」という答えは知っていますが、これをSQLで求めると多くの解答が 「t_connectとm_stationを全てjoinして駅名で絞る」以下のようなSQLではないかと思います。
『一括JOIN 』で求める
select r.route_num, r.line_name from t_connect c1 inner join t_connect c2
on c1.route_num = c2.route_num
inner join m_station s1 on c1.station_id = s1.station_id
inner join m_station s2 on c2.station_id = s2.station_id
inner join m_route r on c1.route_num = r.route_num
where
s1.station_name = ‘新大阪’
and s2.station_name = ‘本町’
私はこれを『一括JOIN』と勝手に呼んでいますが、 若手メンバーの解答も全員がこちらでした。
多くの解説書やサイトの情報でも、このような解答が圧倒的で、これが一般的？ということなのかも知れませんが、以下のようなSQLでも同じ結果が得られます。
『SELECT分割』で求める
select so.route_num, r.line_name from
(select c.route_num from m_station s inner join t_connect c
on s.station_id = c.station_id where s.station_name = ‘新大阪’ )so
inner join
(select c.route_num from m_station s inner join t_connect c
on s.station_id = c.station_id where s.station_name = ‘本町’)hn
on so.route_num = hn.route_num
inner join m_route r on so.route_num = r.route_num
こちらは「対象の駅名をそれぞれ求めてから最後にそれらをjoinする」というアプローチになります。私は、これを『SELECT分割 』と呼んでいます。
私は業務でも、このアプローチで求めることが多いのですが、その理由は・・・
- 内側の朱書きの selectが分割できることで、小さい単位で結果を得ることができデバッグがし易い
- joinによる性能劣化の切り分け判断がし易い
- 処理単位が分割しているので、何をどのように取得しようとしているか他者が理解し易い
等があります。
「１」は、これぐらいのSQLであれば、前者でも良いと思いますが、業務で使用するものは数倍複雑なものがあり「joinの数珠繋ぎ」 で何とかクリアしても「２」の性能が思うように出ない罠にハマることがあります。
対して『SELECT分割 』は、『コピペ』で途中のselect結果が容易に確認でき、他者への説明と理解も早くなります。
また、条件として更に経由駅として「中津」を入れる必要になった場合どうでしょう。
『一括JOIN』では、私はもう手直しする気は起りませんが『SELECT分割』では、以下のように「中津」のselectを追加することで済ませることができます。
条件をselectで追加
select so.route_num, r.line_name from
(select c.route_num from m_station s inner join t_connect c
on s.station_id = c.station_id where s.station_name = ‘新大阪’ )so
inner join
(select c.route_num from m_station s inner join t_connect c
on s.station_id = c.station_id where s.station_name = ‘本町’)hn
inner join
(select c.route_num from m_station s inner join t_connect c
on s.station_id = c.station_id where s.station_name = ‘中津’)nk
on so.route_num = hn.route_num
inner join m_route r on so.route_num = r.route_num
「selectが多い分、処理は遅いのでは？」と思われる人もいるかも知れませんが、selectで多く時間が所要されるのは『コストの量』となります。
『SELECT分割』では、先に駅名で絞っているので最終的に抽出コストは変わらないと言えます。
尚、MySQLでは実行計画は全く同じでした。
これは、オプティマイザが最適化した結果かも知れないので何とも言えませんが、他のデータベースでもこれまでの経験上、『SELECT分割』が原因で処理遅延で困ったことはなく、むしろ『一括JOIN』は、複数インデックスの副作用でキーが思うように当たらないことがあり、その性能対策として『SELECT分割』にて回避したことは幾度かあるので性能対策としてもコントロールしやすい記法だと考えています。