Summary: SQLのJoinの種類と区別。
SQLのJoin
Left, Right, Inner, Outer Joinについて
下記１図でLeft Join, Right Join, Inner Join, Outer Joinの理解ができます。
Where条件によって、青い部分の変化がありますので、ご注意ください。
例えば：普通のLeft joinと条件「Where B.Key is null」を付いているLeft Joinの違うは下記図に左側上から2つ画像をご覧ください。もちろんRight Joinも同じ状況があります。
Cross Joinについて
Cross Joinは前の結合方法と違って、ONでマッチ条件を指定しません。
「左テーブル」 CROSS JOIN 「右テーブル」 のように結合すると、「左テーブル」 と 「右テーブル」の両方のテーブルの、全てのコンビネーションの行の結果セットを取得することができます。
ですので、結果で得られるレコード数は 「 左のテーブルのレコード数 」かける 「 右のテーブルのレコード数 」 になります。
下記図はCross Joinのイメージです。
Joinのアルゴリズム
Joinのアルゴリズムは3種類があり、SQL-ServerとOracleは同じ3種類全部サポートされています。MySQLはNested Loop Joinのみだそうです。
これから3種類のJoinのアルゴリズムの違いを説明します。
Loop Join
一方の結合入力が少なく (たとえば 10 行未満)、他方の結合入力が多く、その結合列にインデックスが設定されている場合、インデックスの入れ子になったループが最も高速な結合演算です。入れ子になったループは I/O が最も少なく、比較が最も少なくなるためです。
入れ子になっているループ結合は “入れ子化反復処理” とも呼ばれ、一方の結合入力を外部入力テーブル (グラフィカルな実行プランでは上部入力として表示) として使用し、もう一方を内部 (下部) 入力テーブルとして使用します。 外部ループでは、外部入力テーブルを 1 行ずつ使用します。 内部ループは、外部行の 1 行ごとに実行され、内部入力テーブルで一致行を検索します。1
内部テーブル（内部ループの対象）に検索条件項目にIndexがついていない場合、全テーブルをスキャンし、一致行を検索します。もちろん条件項目にIndexを追加すれば、パフォーマンス向上できますので、SQLチューニングする時、ご注意ください。
Loop Joinのアルゴリズムは、下記動画のイメージです2。
Merge Join
Merge Joinはまず対象テーブルのデータを条件の順番で並べることが必要ですので、Join条件がPKの場合すでにPKの順番で並んでいるので、Merge Joinが一番理想な選択です。テーブルのデータ量が多くてかつJoin条件がPKやSorted Index以外の場合、Merge Joinが時間かかります。この時Merge Joinじゃなくて、Hash Joinがおすすめです。
MicrosoftオフィシャルサイトにMerge Join関する説明の抜粋下記となります。
2 つの結合入力が少なくない場合でも、結合列に基づいて並べ替えられている場合 (並べ替えられたインデックスのスキャンにより取得された場合など)、マージ結合が最も高速な結合演算です。 両方の結合入力が多く、2 つの入力が同じようなサイズの場合、あらかじめ並べ替えられたマージ結合とハッシュ結合は同じようなパフォーマンスになります。 ただし、2 つの入力サイズが大きく異なる場合、ハッシュ結合演算の方がはるかに高速になることが多くなります。1
Merge Joinのアルゴリズムは、下記動画のイメージです。2
Hash Join
HashはPC専用の言葉だと思います。Hash Joinは名称の意味のような、まずは対象データをHash化し、結果を検索し一致の結果を出力します。
Hash化(Hashの計算方法)は難しくて、私も把握できていないので、趣味ある方下記リンクをご参照ください。
https://ja.wikipedia.org/wiki/ハッシュ関数
MicrosoftオフィシャルサイトにHash Join関する説明の抜粋下記となります。文字の理解が好きではない方直接もっと下の動画イメージで理解してもよいです。
ハッシュ結合は、並べ替えられておらず、インデックスが設定されていない大量の入力を効率的に処理できます。 次のような理由から、ハッシュ結合は、複雑なクエリでの中間結果を得るのに役立ちます。
中間結果にはインデックスが設定されず (ディスクに明示的に保存した後インデックスを設定しない限り)、多くの場合、クエリ プランでの次の演算に合わせて並べ替えられることがありません。
クエリ オプティマイザーは、中間結果のサイズだけを予想します。 複雑なクエリではこの予想が非常に不正確になる場合があります。そのため、中間結果を処理するアルゴリズムは効率的なだけでなく、中間結果が予想をはるかに上回る場合でも、パフォーマンスをあまり低下させないようにする必要があります。
ハッシュ結合では、非正規化の使用を減らすことができます。 通常、非正規化は、結合演算を減らすことにより、パフォーマンスを向上させるときに使用します。ただし、不整合な更新など、データの冗長性が発生するおそれがあります。 ハッシュ結合では、非正規化の必要性が減少します。 ハッシュ結合では、列方向のパーティション分割 (単一テーブルの列グループを異なるファイルまたはインデックスに格納することを表します) を物理データベース デザインに利用できます。1
Hash Joinの実現は下記のイメージです。2
纏め
3種類Joinのアルゴリズムまとめて下記となります。3
| 結合方法 | 仕組み | 利用シーン |
|---|---|---|
| Loop | 一方を外部テーブル（外側のループ）として使用し、もう一方を内部テーブル（内側のループ）として使用します。 外部ループでは、外部入力テーブルを 1 行ずつ使用します。 内部ループは、外部行の 1 行ごとに実行され、内部入力テーブルで一致行を検索します。 | 一方の結合入力が少なく、他方の結合入力が多く、その結合列にインデックスが設定されている場合 |
| Merge | 両方の入力がマージ列を基準に並べ替えられていることが必要です。各入力が並べ替えられるので、は各入力から 1 行ずつ取得して、それらを比較します。行が等しい場合、それらの行が返され、 行が等しくない場合は、小さい方の値が含まれる行が破棄されます。 | 結合列に基づいて並べ替えられている場合 |
| Hash | まず一方の結合列にハッシュ関数にかけ、ハッシュ・テーブルを作成します。次にもう一方の結合列にもハッシュ関数にかけ、結合できるかをハッシュ・テーブルで確認し、ハッシュ値が等しいレコードを結合して結果を返します。 | 並べ替えられておらず、インデックスが設定されていない大量の入力がある場合。特に複雑なクエリでの中間結果を得る場合。等号での比較が前提 |
最後
普通は強制結合指定する必要がないと思います、データサーバーは一般ITエンジニアより賢いだからです。
でも実行プラン一回生成した後、そのまま利用するので、もしテーブルのデータ量や結合対象データなど頻繫に変更する場合、SQLにoption(recomplie)を指定すれば実行プランを再生成できますので、ご利用ください。
もちろん対象テーブルのデータ量やデータ量の変化など十分把握できる方は、指定してもOKです。