こんにちは。
2度目の投稿になります。
今回も前回の記事の続きとして、
SQLによるテーブル結合についてまとめさせていただきます。
前回の記事
テーブル結合についての備忘録
前回のまとめは
SQLのテーブル結合時の順番は、 ONで結合 → WHEREで抽出
LEFT OUTER JOINはONで抽出はできない
といった内容でした。
今回はこれらの続きとしてのTOPICをまとめさせていただきます。
環境:SQL Server 2012
ただし、今回の記事については一概にそうとはいえない内容も含まれています。
環境次第では異なる結果が得られる可能性もあります。
INNER JOINについて
前回の記事で内容に触れたのはLEFT OUTER JOINでした。
RIGHT OUTER JOINはLEFT OUTER JOINを左右反転させただけのもので、
使う機会としてはあまり無いと思いますので説明は割愛します。
今回説明させていただく、
INNER JOINの特徴は以下になります。
INNER JOINでは結合する左右両テーブルで、
条件が満たされていない不要な行を削ぎ落とします。
LEFT OUTER JOINでは問答無用で左側の行を全て呼び出ししていました。
比べて、INNER JOINでは
左右両側で条件が満たされているカラムのみを呼び出しして
不要な行を削ぎ落としします。
また、
両側のテーブルで共通して条件が満たされていないカラムを持つ行は
削ぎ落としされる為に
WHEREの結合条件でも、ONの抽出条件でも、
得られる結合結果は同じものとなります。
つまり、
SELECT
a.name
, b.name
FROM a
INNER JOIN b
ON a.b_cd = b.cd
WHERE
b.cd = '1'
SELECT
a.name
, b.name
FROM a
INNER JOIN b
ON a.b_cd = b.cd
AND b.cd = '1'
以上の①、②の2パターンでどちらも得られる結果は
同じなんですね。
では、この内どちらの方がベターなんでしょうか？
ここからは、個人的な推測も入ってきます。
環境にもよる為に、ケースバイケースなので、
100％とは言えないと思います。
結論から述べると、
可読性を重視するなる①、
パフォーマンスを重視するなら②
になるのかなと思います。
前回の記事で、
結合の際の処理の流れと、
ONとWHEREに書かれるべき条件を示しました。
そう、まずONの条件で結合して、
WHEREの条件で抽出するんでしたね？
仮に大量の10万件ものデータ同士で結合させるとしましょう。
その際にはまず②のパターンのように、
ONで結合する時点で、
不要なデータは取り除いていた方が効率的ですよね。
実際には抽出をかける
テーブルのインデックスの構成に左右されたり
DBMSのオプティマイザが仕事をしてくれる為に
一概に必ず速いとも言えないようです。
個人的な意見としてはロジカルに考えるなら
ONでデータの件数を減らしておいた方がいいのかな
と思います。
しかし、本来はONに書かれるべき条件は結合条件であるべきです。
条件が多く書かれて、テーブルのデータ件数もそんなに無いような時には
結合条件はONへ、抽出条件はWHEREへ書き分けるべきでしょう。
結論が着地できたのか怪しいところですが
大事なところは
INNER JOINではONでも、WHEREでも得られる結果は
共通して条件が満たされていない行は削ぎ落とされる為に変わらない。
ってところですね。
抽出条件を含むLEFT OUTER JOINの最適な結合方法
さて、前回の記事だけでは未解決の部分があります。
前回の記事の冒頭であったこの会話の部分です。
SE1「WHERE句でテーブル結合なんかしたら駄目だよ！
10万件のデータと10万件のデータのテーブル同士をくっ付けるとすごく無駄になるよ！」
私「なるほど！ 先に結合のときに件数を減らしとくわけですね！」
そう。結局、前回の記事の結論ではLEFT OUTER JOINの場合、
ON句では抽出が出来ない為にWHEREへ書くと終わりました。
それでは一週回ってしまってこの問題が解決できていないままですよね。
LEFT OUTER JOIN時にあらかじめ結合の件数を減らしておく為には
どうすればいいのでしょうか？
その答えはサブクエリを使う方法があります。
結合時の処理の順番は
ONの結合条件 → WHEREの抽出条件
となっていましたが、
サブクエリが含まれるSQLの場合は、
サブクエリの()内の処理 → ONの結合条件 → WHEREの抽出条件
というような流れで処理が行われていきます。
具体的な例をあげてみます。
①のSQLをサブクエリを使って書き直したものが②になります。
(更新しました。サブクエリにはWITH使いましょう。)
SELECT
a.name
, b.name
FROM a
LEFT OUTER JOIN b
ON a.b_cd = b.cd
WHERE
b.cd = '1'
WITH aaa AS(
SELECT * FROM a
WHERE a.b_cd = '1'
)
SELECT
aaa.name
, b.name
FROM aaa
LEFT OUTER JOIN b
ON aaa.b_cd = b.cd
②のSQLであれば
①サブクエリの()内の処理 (この時点でa.b_cd = '1'に抽出済み)
②ONによる結合
といった処理の流れとなります。
これにより、
無駄な件数のテーブル結合を防ぐことが出来るうえ、
左側のテーブルはサブクエリによって既に抽出済みのために
LEFT OUTER JOINによって左側のテーブルの行が全て呼び出されたとしても
最終的に呼び出される結果も1行に抽出がされたテーブルとなってくれています。
可読性は著しく落ちてしまいますが
ロジカルに考えるとこれが最も効率的な結合方法になるのかなというのが
今回の件で至った結論になります。
しかしこれまた、
実際には抽出をかける
テーブルのインデックスの構成に左右されたり
DBMSのオプティマイザが仕事をしてくれる為に
一概に必ず速いとも言えないようです。
DRY原則に違反してますね。コードならメソッド化しましょう。
業務で使う場合には実際の環境で各SQLを試してみて、
パフォーマンスにどれほどの差が出るかを確認したうえで
最適なSQLを組んでみるのがいいと思います。