Inner Join Vs Outer Join (内部結合と外部結合)。
Inner Join Vs Outer Join の違いを調べる前に、まず SQL JOIN とは何なのか見てみましょう。
結合句は、結合条件を通して、2つ以上のテーブルからレコードを結合したり、レコードを操作するために使用されます。 結合条件は、各テーブルからの列が互いにどのように照合されるかを示します。
結合は、これらのテーブル間の関連する列に基づいて行われます。 最も一般的な例は、主キー列と外部キー列を使用した2つのテーブル間の結合です。
たとえば、社員の給与を含むテーブルと社員の詳細を含む別のテーブルを持っているとします。 この従業員ID列は、従業員の詳細テーブルの主キーと従業員の給与テーブルの外部キーになります。 テーブルをエンティティと考え、キーは結合操作に使用される2つのテーブル間の共通リンクと考えることができます。
基本的に、SQLには内部結合と外部結合という2種類の結合があります。 外部結合はさらに、左外部結合、右外部結合、完全外部結合の3種類に細分化されます。
この記事では、内部結合と外部結合の違いについて詳しく見ていきます。 クロスジョインと不等間隔ジョインについてはこの記事の範囲外とします。
内部ジョインとは何ですか。
内部ジョインは両方のテーブルで一致する値を持つ行だけを返します（ここでは、ジョインは2つのテーブル間で行われると考えています）。
外部結合には3つのタイプがあります：
- Left Outer Join。
- 右外部結合：左テーブルからすべての行を返し、両テーブル間で一致するレコードを返します。 右外部結合： 右テーブルのすべての行と、両テーブル間の一致するレコードを返します。
Difference between Inner and Outer Join
上の図に示すように、二つのエンティティ、すなわちテーブル1とテーブル2があって、両方のテーブルはいくつかの共通のデータを共有しています。
内部結合はこれらのテーブル間の共通領域（上の図の緑色の網掛け部分）、つまり、テーブル1とテーブル2の間で共通するすべてのレコードを返します。
左外部結合はテーブル1からのすべての行とテーブル1にも共通するそれらの行のみをテーブル2から返します。 右外部結合はその逆を行います。
さらに、完全外部結合では、テーブル1とテーブル2からのすべてのレコードが返されます。 EmpDetailsとEmpSalaryの2つのテーブルがあるとします。
EmpDetailsテーブルです。
| EmployeeID | EmployeeName |
| 1 | John |
| 2 | Samantha |
| 3 | Hakuna |
| 4 | Silky |
| 5 | ラム |
| 6 | |
| 7 | |
| 8 | シータ |
| 9 | Farah |
| 10 | Jerry |
EmpSalaryテーブルを表示します。
| EmployeeID | EmployeeName | EmployeeSalary |
|---|---|---|
| 1 | John | 50000 |
| 2 | Samantha | 120000 |
| 3 | ハクナ | 75000 |
| 4 | 25000 | |
| 5 | 150000 | |
| 6 | Arpit | 80000 |
| 11 | Rose | 90000 |
| 12 | Sakshi | 45000 |
| 13 | Jack | 250000 |
これら二つのテーブルで内部結合を行って結果を観察してみましょう。
クエリ:
SELECT EmpDetails. EmployeeID, EmpDetails. EmployeeName, EmpSalary. EmployeeSalaryFROM EmpDetails INNER JOIN EmpSalaryON EmpDetails. EmployeeID = EmpSalary. EmployeeID;
結果。
| EmployeeID | EmployeeName | EmployeeSalary | |
|---|---|---|---|
| 1 | John | 50000 | |
| 2 | Samantha | 120000 | |
| 3 | Hakuna | 75000 | |
| 4 | Silky | 25000 | |
| 5 | 3706 | ラム | 150000 |
| 6 | アーピット | 80000 |
上記の結果セットにおいて。 内部結合により、EmpDetailsとEmpSalaryの両方に存在する、キーが一致する最初の6レコードが返されたことがわかります。すなわち、EmployeeIDです。 したがって、AとBが2つのエンティティである場合、Inner Joinは、一致するキーに基づいて、「AとBのレコード」に等しい結果セットを返します。
次に、Left Outer Joinが何を行うか見ていきましょう。
クエリー：
SELECT EmpDetails. EmployeeID, EmpDetails. EmployeeName, EmpSalary. EmployeeSalaryFROM EmpDetails LEFT JOIN EmpSalaryON EmpDetails. EmployeeID = EmpSalary. EmployeeID;
結果。
| EmployeeID | EmployeeName | EmployeeSalary |
|---|---|---|
| 1 | John | 50000 |
| 2 | Samantha | 120000 |
| 3 | Hakuna | 75000 |
| 4 | Silky | 25000 |
| 5 | Ram | 150000 |
| 6 | Arpit | 80000 |
| 7 | Lily | NULL |
| 8 | Sita | NULL |
| 9 | Farah | NULL |
| 10 | Jerry | NULL |
上記の結果セットでは。 左外部結合がLEFTテーブルから10レコードをすべて返したことがわかります。残りのレコードは右側のテーブル、つまりEmpSalaryテーブルに一致するキーがないため、それらに対応するNULLが返されました。 Lily、Sita、Farah、JerryはEmpSalaryテーブルに一致する社員IDを持っていないので、彼らのSalaryは結果セットにNULLと表示されます。
したがって、AおよびBが2つのエンティティであれば、左外部結合は一致するキーに基づいて「Aのレコード NOT B」に等しい結果セットを返します。
クエリ:
SELECT EmpDetails. EmployeeID, EmpDetails. EmployeeName, EmpSalary. EmployeeSalaryFROM EmpDetails RIGHT join EmpSalaryON EmpDetails. EmployeeID = EmpSalary. EmployeeID;
結果:
| EmployeeID | EmployeeName | EmployeeSalary |
|---|---|---|
| 1 | John | 50000 |
| 2 | Samantha | 120000 |
| 3 | Hakuna | 75000 |
| 4 | Silky | 25000 |
| 5 | Ram | 150000 |
| 6 | Arpit | 80000 |
| NULL | 90000 | |
| NULL | 250000 | |
| NULL | 250000 |
上記の結果セットでは、
と
があります。 右外部結合が左結合の逆を行ったことがわかります。 しかし、Rose、Sakshi、Jackは左側のテーブル（EmpDetailsテーブル）に一致する社員IDを持っていないので、左側のテーブルから社員IDと社員名をNULLとして取得しました。
したがって、AとBが2つのエンティティである場合、右外部結合は一致するキーに基づいて、「B NOT Aのレコード」に等しい結果セットを返すことになります。
クエリ:
SELECT *FROM EmpDetails RIGHT JOIN EmpSalaryON EmpDetails. EmployeeID = EmpSalary. EmployeeID;
結果:
| EmployeeID | EmployeeName | EmployeeID | EmployeeName従業員給与 | ||
|---|---|---|---|---|---|
| 1 | John | 50000 | |||
| 2 | Samantha | 120000 | |||
| 3 | Hakuna | Samantha3706 | 3 | Hakuna | 75000 |
| 4 | Silky | 25000 | |||
| 5 | 150000 | ||||
| 6Arpit | 6 | 80000 | |||
| NULL | NULL | 90000 | |||
| NULL | 12 | Sakshi | 250000 | ||
| NULL | 13 | Jack | 250000 |
さて、次は。 フルジョインに移行しましょう。
完全外部結合は、一致するかどうかに関係なく、両方のテーブルからすべてのデータを取得したい場合に行います。 したがって、一致するキーが見つからなくても、すべての従業員が必要な場合、次のようにクエリを実行します。
Query:
SELECT *FROM EmpDetails FULL JOIN EmpSalaryON EmpDetails. EmployeeID = EmpSalary. EmployeeID;
Result:
| EmployeeID | EmployeeName | EmployeeSalary | ||
|---|---|---|---|---|
| 1 | John | 1 | 50000 | |
| 2 | Samantha | 120000 | ||
| 3 | John | 5000Hakuna | 3 | 75000 |
| 4 | Silky | 25000 | ||
| 5 | 4Ram | 150000 | ||
| 6 | Arpit | 80000 | ||
| 7 | Lily | NULL | NULL | |
| 8 | Sita | NULL | NULL | |
| 9Farah | NULL | NULL | ||
| 10 | Jerry | NULL | NULL | |
| NULLNULL | 11 | Rose | 90000 | |
| NULL | 12 | Sakshi | 250000 | |
| NULL | NULL | 13 | Jack | 250000 |
上記の結果セットで、最初の6レコードとして両方のテーブルで一致しているのがわかる。 NULLなしですべてのデータを取得しました。
最後の3つのレコードは右のテーブルに存在し、左のテーブルには存在しないので、左のテーブルの対応するデータはNULLになります。 したがって、AとBが2つのエンティティである場合、完全外部結合は一致するキーに関係なく、「A AND Bのレコード」に等しい結果セットを返します。
理論的には、これは左結合と右結合を組み合わせたものです。 定義によると、外側joinは左または右であれ、内側joinのすべての作業と、結果を拡張する追加作業を実行しなければなりません。 外側joinはより多くのレコードを返すと予想され、結果セットが大きくなるため、総実行時間がさらに長くなります。
さらに、左結合が内側結合よりも高速になる特定の状況もありますが、左外側結合は内側結合と機能的に同等ではないので、それらを互いに置き換えることはできません。 結合操作に関係するテーブルが小さすぎる場合、例えばレコード数が10未満で、テーブルがクエリをカバーするのに十分なインデックスを持っていない場合、一般的に左結合は内側結合より高速になります。
CREATE TABLE #Table1(ID int NOT NULL PRIMARY KEY,Name varchar(50) NOT NULL)INSERT #Table1 (ID, Name) VALUES (1, 'A')INSERT #Table1 (ID, Name) VALUES (2, 'B')INSERT #Table1 (ID, Name) VALUES (3, 'C')INSERT #Table1 (ID, Name) VALUES (4, 'D')INSERT #Table1 (ID, Name) VALUES (5, 'E')CREATE TABLE #Table2(ID int NOT NULL PRIMARY KEY,Name varchar(50) NOT NULL)INSERT #Table2 (ID, Name) VALUES (1, 'A')INSERT #Table2 (ID, Name) VALUES (2, 'B')INSERT #Table2 (ID, Name) VALUES (3, 'C')INSERT #Table2 (ID, Name) VALUES (4, 'D')INSERT #Table2 (ID, Name) VALUES (5, 'E')SELECT *FROM #Table1 t1INNER JOIN #Table2 t2ON t2.Name = t1.Name
| ID | Name | |||
|---|---|---|---|---|
| ID | Name | ID | Name | |
| 1 | 1 | a | ||
| 2 | b | 2 | b | |
| 3 | c | c | ||
| 4 | d | 4 | d | |
| 5 | 5 | e |
SELECT * FROM (SELECT 38 AS bah) AS foo JOIN (SELECT 35 AS bah) AS barON (55=55);
| ID | Name | |||||
|---|---|---|---|---|---|---|
| 1 | ID | Name1 | a | 1 | ||
| 2 | 2 | b | b | 3 | c | c |
| 4 | d | 4D | ||||
| 5 | E |
上のようになりますね。 両方のクエリから同じ結果セットが返されました。 この場合、両方のクエリの実行プランを見ると、内側joinの方が外側joinよりもコストがかかっていることがわかります。 これは、左結合では入れ子ループを実行するのに対して、内側結合ではハッシュマッチを実行するためです。 しかし、このケースでは、行数が非常に少なく、使用するインデックスがないため（名前列で結合しているため）、ハッシュ演算は最も高価な内部結合クエリになっています。
ただし、結合クエリの一致キーを名前からIDに変更すると、テーブル内に多数の行がある場合は、内部結合が左外部結合より速くなることが判明します。
MS Accessの内部および外部結合
MS Accessクエリで複数のデータソースを使用する場合、データソースが互いにリンクされている方法に応じて、表示するレコードを制御するためにJOINを適用します。 これは Access のデフォルトの結合であり、最も頻繁に使用されるものです。
外部結合では、両方のテーブルからすべての関連するデータが正しく結合され、1つのテーブルからすべての残りの行が結合されます。 完全外側joinでは、すべてのデータは可能な限り結合されます。
Left Join vs Left Outer Join
SQLサーバーでは、左外側joinを適用する場合、outerキーワードはオプションです。 したがって、’LEFT OUTER JOIN’ または ‘LEFT JOIN’ と書いても、どちらも同じ結果になるため違いはありません。
以下は、SQL サーバーにおける同等の構文のリストです：
Left Outer Join vs Right Outer Join
この記事で、すでにこの違いを見てきました。 左外部結合と右外部結合のクエリーと結果セットを参照して、その違いを確認することができます。
左結合と右結合の主な違いは、一致しない行を含めるかどうかにあります。 左外部結合では、結合句の左側にあるテーブルからマッチしない行が含まれ、右外部結合では、結合句の右側にあるテーブルからマッチしない行が含まれます。 基本的に、これらは引数が逆になっている以外は同じタイプのオペレーションです。 したがって、どちらの結合を使用するかを尋ねる場合、実際には a<b と b>a のどちらを書くべきかを尋ねていることになります。 これは好みの問題です。
一般的に、人々はSQLクエリで左結合を使用することを好みます。 クエリの解釈における混乱を避けるために、クエリを記述する方法に一貫性を保つことをお勧めします。
これまで内部結合とすべてのタイプの外部結合について見てきました。
Difference between Inner Join and Outer Join in Tabular Format
| Inner Join | Outer Join |
|---|---|
| Rowes only that have matching values in both the table.は、両方のテーブルの値が一致する行を返します。 | 2 つのテーブル間で一致する行と一致しない行を含みます。 |
| テーブル内に多数の行があり、使用するインデックスがある場合、INNER JOIN は通常 OUTER JOIN よりも高速になります。 | |
| 一致しない場合、何も返しません。 | 一致しない場合、返される列値には NULL が置かれます。 |
| INNER JOINは、特定の列の詳細情報を調べたいときに使います。 | OUTER JOINは、二つのテーブルのすべての情報のリストを表示したいときに使います。 |
| INNER JOINはフィルターのような働きをします。 | They act like data-add on. |
| Implicit join notation exists for inner join which enlists tables to be joined in the comma separated manner in the FROM clause.FROM句で、内部結合のための一致がなければなりません。 例 SELECT * FROM product, category WHERE product.CategoryID = category.CategoryID; |
外部結合には暗黙の結合記法はありません。 |
以下は内部結合のビジュアライゼーションです。 |
Below is the visualization of an outer join |
内部および外部結合と結合
時に、結合および結合は間違われ、SQLインタビューで最も多く聞かれる質問の1つにこれが含まれます。 我々はすでに内側joinと外側joinの違いを見てきました。
UNIONがクエリの行を互いに後に配置するのに対し、JOINはデカルト積を作成し、それをサブセットします。 したがって、UNION と JOIN は完全に異なる操作です。
MySQL で以下の 2 つのクエリを実行し、その結果を見てみましょう。
UNION クエリ:
SELECT 28 AS bahUNIONSELECT 35 AS bah;
Result:
| Bah | |
|---|---|
| 1 | 28 |
| 2 | 35 |
JOIN Query:
SELECT * FROM(SELECT 38 AS bah) AS fooJOIN(SELECT 35 AS bah) AS barON (55=55);
結果。
| foo | Bar | |
|---|---|---|
| 1 | 38 | 35 |
UNION操作は、2つ以上のクエリーの結果を1つの結果セットにまとめます。 この結果セットには、UNIONに含まれるすべてのクエリを通じて返されたすべてのレコードが格納されます。 したがって、基本的に UNION は 2 つの結果セットを一緒に結合します。
結合操作は、これらのテーブル間の論理的関係に基づいて、つまり結合条件に基づいて 2 つ以上のテーブルからデータをフェッチします。 結合クエリでは、あるテーブルからのデータは、別のテーブルからレコードを選択するために使用されます。 非常に簡単に理解するために、UNIONは2つのテーブルから行を結合するのに対し、joinは2つ以上のテーブルから列を結合すると言うことができます。 このように、どちらも n 個のテーブルからデータを結合するために使用されますが、データがどのように結合されるかが異なります。
上記は、結果セット内の各レコードが両方のテーブル（テーブルAとテーブルB）からの列を含むことを示すJoin操作の図解表現です。
結合は一般に非正規化（正規化の反対）の結果であり、あるテーブルの外部キーを使用して、別のテーブルの主キーを使用して列値を検索します。
上記はUNION操作の図解ですが、これは、結果セット内のそれぞれのレコードが、どちらかのテーブルからの行であると説明しています。 このように、UNIONの結果は、テーブルAとテーブルBからの行を結合しています。
詳しくはこちら =>> MySQL UNION 解説と例
結論
この記事では、SQLの内部結合と外部結合間の大きな違いを見てきました。 また、外部結合の分類（左結合、右結合、完全結合）も見てきました。 これらの結合タイプがどのように動作し、どのように互いに異なるかを見てきました。
また、これらの結合タイプ間のパフォーマンス比較も行いました。 また、joinがunionとどのように異なるかについても説明しました。=>> MySQL Join Types
この記事は、さまざまなjoinタイプの違いに関する疑問の解消に役立ったことでしょう。 この記事によって、必要な結果セットに基づいてどの結合タイプを選択するかを決定することができると確信しています。