inner join vs outer join
内部結合と外部結合：内部結合と外部結合の正確な違いを調べる準備をしてください
内部結合と外部結合の違いを調べる前に、まずSQL結合とは何かを見てみましょう。
結合句は、レコードを結合したり、結合条件を介して2つ以上のテーブルのレコードを操作したりするために使用されます。結合条件は、各テーブルの列が互いにどのように一致するかを示します。
結合は、これらのテーブル間の関連する列に基づいています。最も一般的な例は、主キー列と外部キー列を介した2つのテーブル間の結合です。
従業員の給与を含むテーブルがあり、従業員の詳細を含む別のテーブルがあるとします。
この場合、これら2つのテーブルを結合する従業員IDのような共通の列があります。この従業員ID列は、従業員詳細テーブルの主キーと従業員給与テーブルの外部キーになります。
2つのエンティティ間で共通のキーを持つことが非常に重要です。テーブルはエンティティと見なすことができ、キーは結合操作に使用される2つのテーブル間の共通リンクと考えることができます。
Windows 102017用の最高の無料バックアップソフトウェア
基本的に、SQLには2つのタイプの結合があります。 内部結合と外部結合 。外部結合はさらに3つのタイプに細分されます。 左外部結合、右外部結合、および完全外部結合。
この記事では、の違いを見ていきます 内部結合と外部結合 詳細に。クロス結合と不等結合は、この記事の範囲外にします。
学習内容：
- 内部結合とは何ですか？
- アウタージョインとは何ですか？
- 内部結合と外部結合の違い
- パフォーマンス
- MSAccessの内部結合と外部結合
- 左結合と左外部結合
- 左外部結合と右外部結合
- 表形式の内部結合と外部結合の違い
- 内側と外側の結合vsユニオン
- 結論
- 推奨読書
内部結合とは何ですか？
内部結合は、両方のテーブルで値が一致する行のみを返します（ここでは、2つのテーブル間で結合が行われることを検討しています）。
アウタージョインとは何ですか？
外部結合には、一致する行と、2つのテーブル間で一致しない行の一部が含まれます。外部結合は基本的に、誤った一致条件を処理する方法が内部結合と異なります。
外部結合には次の3つのタイプがあります。
- 左外部結合 ：LEFTテーブルのすべての行と、両方のテーブル間で一致するレコードを返します。
- 右外部結合 ：RIGHTテーブルのすべての行と、両方のテーブル間で一致するレコードを返します。
- 完全外部結合 ：左外部結合と右外部結合の結果を組み合わせます。
内部結合と外部結合の違い
(画像 ソース )
上の図に示すように、テーブル1とテーブル2の2つのエンティティがあり、両方のテーブルがいくつかの共通データを共有しています。
内部結合は、これらのテーブル間の共通領域（上の図の緑色の影付き領域）、つまりテーブル1とテーブル2の間で共通のすべてのレコードを返します。
左外部結合は、テーブル1のすべての行と、テーブル1にも共通のテーブル2の行のみを返します。右外部結合は正反対のことをします。テーブル2のすべてのレコードと、テーブル1の対応する一致するレコードのみが表示されます。
さらに、完全外部結合により、表1と表2のすべてのレコードが得られます。
これを明確にするための例から始めましょう。
2つあるとしましょう テーブル： EmpDetailsとEmpSalary 。
EmpDetailsテーブル：
| 従業員ID | 従業員名 |
| 7 | リリー |
| 1 | ジョン |
| 二 | サマンサ |
| 3 | なし |
| 4 | シルキー |
| 5 | RAM |
| 6 | Arpit |
| 8 | シタ |
| 9 | ファラ |
| 10 | ジェリー |
EmpSalaryテーブル：
| 従業員ID | 従業員名 | EmployeeSalary |
|---|---|---|
| 十一 | ローズ | 90000 |
| 1 | ジョン | 50,000 |
| 二 | サマンサ | 120000 |
| 3 | なし | 75000 |
| 4 | シルキー | 25000 |
| 5 | RAM | 150000 |
| 6 | Arpit | 80000 |
| 12 | サクシ | 45000 |
| 13 | ジャック | 250,000 |
これらの2つのテーブルで内部結合を実行し、結果を観察してみましょう。
クエリ：
SELECT EmpDetails. EmployeeID, EmpDetails. EmployeeName, EmpSalary. EmployeeSalary FROM EmpDetails INNER JOIN EmpSalary ON EmpDetails. EmployeeID = EmpSalary. EmployeeID; 結果：
| 従業員ID | 従業員名 | EmployeeSalary |
|---|---|---|
| 7 | リリー | ヌル |
| 1 | ジョン | 50,000 |
| 二 | サマンサ | 120000 |
| 3 | なし | 75000 |
| 4 | シルキー | 25000 |
| 5 | RAM | 150000 |
| 6 | Arpit | 80000 |
上記の結果セットでは、内部結合が、EmpDetailsとEmpSalaryの両方に存在し、一致するキー（EmployeeID）を持つ最初の6つのレコードを返したことがわかります。したがって、AとBが2つのエンティティである場合、内部結合は、一致するキーに基づいて、「AとBのレコード」に等しい結果セットを返します。
ここで、左外部結合が何をするかを見てみましょう。
クエリ：
SELECT EmpDetails. EmployeeID, EmpDetails. EmployeeName, EmpSalary. EmployeeSalary FROM EmpDetails LEFT JOIN EmpSalary ON EmpDetails. EmployeeID = EmpSalary. EmployeeID; 結果：
| 従業員ID | 従業員名 | EmployeeSalary |
|---|---|---|
| 1 | ジョン | 50,000 |
| 二 | サマンサ | 120000 |
| 3 | なし | 75000 |
| 4 | シルキー | 25000 |
| 5 | RAM | 150000 |
| 6 | Arpit | 80000 |
| 8 | シタ | ヌル |
| 9 | ファラ | ヌル |
| 10 | ジェリー | ヌル |
上記の結果セットでは、左外部結合がLEFTテーブル（EmpDetailsテーブル）から10個のレコードすべてを返し、最初の6個のレコードが一致しているため、これらの一致するレコードの従業員の給与を返していることがわかります。
残りのレコードには、RIGHTテーブル（EmpSalaryテーブル）に一致するキーがないため、それらに対応するNULLが返されました。 Lily、Sita、Farah、およびJerryのEmpSalaryテーブルには一致する従業員IDがないため、結果セットでは給与がNULLとして表示されます。
したがって、AとBが2つのエンティティである場合、左外部結合は、一致するキーに基づいて、「Records in ANOTB」に等しい結果セットを返します。
ここで、右外部結合が何をするかを観察しましょう。
クエリ：
SELECT EmpDetails. EmployeeID, EmpDetails. EmployeeName, EmpSalary. EmployeeSalary FROM EmpDetails RIGHT join EmpSalary ON EmpDetails. EmployeeID = EmpSalary. EmployeeID; 結果：
| 従業員ID | 従業員名 | EmployeeSalary |
|---|---|---|
| ヌル | ヌル | 90000 |
| 1 | ジョン | 50,000 |
| 二 | サマンサ | 120000 |
| 3 | なし | 75000 |
| 4 | シルキー | 25000 |
| 5 | RAM | 150000 |
| 6 | Arpit | 80000 |
| ヌル | ヌル | 250,000 |
| ヌル | ヌル | 250,000 |
上記の結果セットでは、右外部結合が左結合の正反対を行っていることがわかります。右側のテーブル、つまりEmpSalaryテーブルからすべての給与が返されました。
ただし、Rose、Sakshi、およびJackの左側のテーブル（EmpDetailsテーブル）には一致する従業員IDがないため、左側のテーブルから従業員IDとEmployeeNameをNULLとして取得しました。
したがって、AとBが2つのエンティティである場合、右外部結合は、一致するキーに基づいて、「Records in BNOTA」に等しい結果セットを返します。
また、両方のテーブルのすべての列で選択操作を実行した場合の結果セットを確認しましょう。
クエリ：
SELECT * FROM EmpDetails RIGHT JOIN EmpSalary ON EmpDetails. EmployeeID = EmpSalary. EmployeeID;結果：
| 従業員ID | 従業員名 | 従業員ID | 従業員名 | EmployeeSalary |
|---|---|---|---|---|
| ヌル | ヌル | 十一 | ローズ | 90000 |
| 1 | ジョン | 1 | ジョン | 50,000 |
| 二 | サマンサ | 二 | サマンサ | 120000 |
| 3 | なし | 3 | なし | 75000 |
| 4 | シルキー | 4 | シルキー | 25000 |
| 5 | RAM | 5 | RAM | 150000 |
| 6 | Arpit | 6 | Arpit | 80000 |
| ヌル | ヌル | 12 | サクシ | 250,000 |
| ヌル | ヌル | 13 | ジャック | 250,000 |
それでは、完全結合に移りましょう。
完全外部結合は、一致するかどうかに関係なく、両方のテーブルのすべてのデータが必要な場合に実行されます。したがって、一致するキーが見つからなくてもすべての従業員が必要な場合は、次のようにクエリを実行します。
クエリ：
SELECT * FROM EmpDetails FULL JOIN EmpSalary ON EmpDetails. EmployeeID = EmpSalary. EmployeeID;結果：
| 従業員ID | 従業員名 | 従業員ID | 従業員名 | EmployeeSalary |
|---|---|---|---|---|
| 7 | リリー | ヌル | ヌル | ヌル |
| 1 | ジョン | 1 | ジョン | 50,000 |
| 二 | サマンサ | 二 | サマンサ | 120000 |
| 3 | なし | 3 | なし | 75000 |
| 4 | シルキー | 4 | シルキー | 25000 |
| 5 | RAM | 5 | RAM | 150000 |
| 6 | Arpit | 6 | Arpit | 80000 |
| 8 | シタ | ヌル | ヌル | ヌル |
| 9 | ファラ | ヌル | ヌル | ヌル |
| 10 | ジェリー | ヌル | ヌル | ヌル |
| ヌル | ヌル | 十一 | ローズ | 90000 |
| ヌル | ヌル | 12 | サクシ | 250,000 |
| ヌル | ヌル | 13 | ジャック | 250,000 |
上記の結果セットでは、最初の6つのレコードが両方のテーブルで一致しているため、NULLなしですべてのデータを取得していることがわかります。次の4つのレコードは左側のテーブルには存在しますが、右側のテーブルには存在しないため、右側のテーブルの対応するデータはNULLです。
最後の3つのレコードは、左側のテーブルではなく右側のテーブルに存在するため、左側のテーブルの対応するデータにはNULLがあります。したがって、AとBが2つのエンティティである場合、完全外部結合は、一致するキーに関係なく、「Records in AANDB」と等しい結果セットを返します。
理論的には、左結合と右結合の組み合わせです。
パフォーマンス
SQLサーバーの内部結合と左外部結合を比較してみましょう。操作の速度について言えば、左外部結合は明らかに内部結合よりも高速ではありません。
定義によると、外部結合は、左でも右でも、結果をnull拡張する追加の作業とともに内部結合のすべての作業を実行する必要があります。外部結合は、より多くのレコードを返すことが期待されます。これにより、結果セットが大きくなるという理由だけで、合計実行時間がさらに長くなります。
したがって、外部結合は内部結合よりも低速です。
さらに、左結合が内部結合よりも高速になる特定の状況がいくつかありますが、左外部結合は機能的に内部結合と同等ではないため、それらを相互に置き換えることはできません。
左結合が内部結合よりも高速である可能性がある場合について説明します。結合操作に関係するテーブルが小さすぎる場合、たとえば、レコード数が10未満で、テーブルにクエリをカバーするのに十分なインデックスがない場合、通常、左結合は内部結合よりも高速です。
以下の2つのテーブルを作成し、例としてそれらの間でINNERJOINとLEFTOUTERJOINを実行してみましょう。
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 t1 INNER JOIN #Table2 t2 ON t2.Name = t1.Name | ID | 名前 | ID | 名前 | |
|---|---|---|---|---|
以下は、内部結合の視覚化です。  | 以下は、外部結合の視覚化です  | |||
| 1 | 1 | に | 1 | に |
| 二 | 二 | B | 二 | B |
| 3 | 3 | C | 3 | C |
| 4 | 4 | D | 4 | D |
| 5 | 5 | IS | 5 | IS |
SELECT * FROM (SELECT 38 AS bah) AS foo JOIN (SELECT 35 AS bah) AS bar ON (55=55); | ID | 名前 | ID | 名前 | |
|---|---|---|---|---|
| 1 | 1 | に | 1 | に |
| 二 | 二 | B | 二 | B |
| 3 | 3 | C | 3 | C |
| 4 | 4 | D | 4 | D |
| 5 | 5 | IS | 5 | IS |
上記のように、両方のクエリが同じ結果セットを返しました。この場合、両方のクエリの実行プランを表示すると、内部結合のコストが外部結合よりも高いことがわかります。これは、内部結合の場合、SQLサーバーはハッシュ一致を実行するのに対し、左結合の場合はネストされたループを実行するためです。
ハッシュ一致は通常、ネストされたループよりも高速です。ただし、この場合、行数が非常に少なく、使用するインデックスがないため（名前列で結合を実行しているため）、ハッシュ操作は最もコストのかかる内部結合クエリであることが判明しました。
ただし、結合クエリの一致するキーを名前からIDに変更し、テーブルに多数の行がある場合は、内側の結合が左側の外側の結合よりも高速であることがわかります。
MSAccessの内部結合と外部結合
MS Accessクエリで複数のデータソースを使用する場合は、データソースが相互にリンクされている方法に応じて、JOINを適用して表示するレコードを制御します。
内部結合では、両方のテーブルの関連するものだけが1つの結果セットに結合されます。これはAccessのデフォルトの結合であり、最も頻繁に使用される結合でもあります。結合を適用するが、それがどのタイプの結合であるかを明示的に指定しない場合、Accessはそれが内部結合であると見なします。
外部結合では、両方のテーブルのすべての関連データに加えて、1つのテーブルの残りのすべての行が正しく結合されます。完全外部結合では、すべてのデータが可能な限り結合されます。
左結合と左外部結合
SQL Serverでは、左外部結合を適用する場合、キーワードouterはオプションです。したがって、「LEFTOUTERJOIN」または「LEFTJOIN」のどちらを記述しても同じ結果が得られるため、違いはありません。
LEFT JOIN Bは、A LEFT OUTER JOINBと同等の構文です。
以下は、SQLサーバーの同等の構文のリストです。
(画像 ソース )
左外部結合と右外部結合
この記事では、この違いをすでに見てきました。左外部結合と右外部結合のクエリと結果セットを参照して、違いを確認できます。
左結合と右結合の主な違いは、一致しない行が含まれていることです。左外部結合には、結合句の左側にあるテーブルの一致しない行が含まれますが、右外部結合には、結合句の右側にあるテーブルの一致しない行が含まれます。
左結合と右結合のどちらを使用するのが良いかを尋ねられますか？基本的に、引数が逆になっていることを除いて、同じタイプの操作です。したがって、どの結合を使用するかを尋ねるとき、実際には、 a。それは好みの問題です。
一般に、SQLクエリでは左結合を使用することを好みます。クエリの解釈の混乱を避けるために、クエリの記述方法に一貫性を保つことをお勧めします。
これまで、内部結合とすべてのタイプの外部結合について見てきました。内部結合と外部結合の違いを簡単に要約しましょう。
表形式の内部結合と外部結合の違い
| 内部結合 | アウタージョイン |
|---|---|
| 両方のテーブルで値が一致する行のみを返します。 | 一致する行と、2つのテーブル間の一致しない行の一部が含まれます。 |
| テーブルに多数の行があり、使用するインデックスがある場合、INNERJOINは一般にOUTERJOINよりも高速です。 | 一般に、OUTER JOINは、INNER JOINと比較してより多くのレコードを返す必要があるため、INNERJOINよりも低速です。ただし、OUTERJOINの方が速い特定のシナリオがいくつかあります。 |
| 一致するものが見つからない場合、何も返されません。 | 一致するものが見つからない場合、返された列の値にNULLが入れられます。 |
| 特定の列の詳細情報を検索する場合は、INNERJOINを使用します。 | 2つのテーブルのすべての情報のリストを表示する場合は、OUTERJOINを使用します。 |
| INNERJOINはフィルターのように機能します。内部結合がデータを返すには、両方のテーブルが一致している必要があります。 | それらはデータアドオンのように機能します。 |
| FROM句でコンマ区切りの方法で結合されるテーブルをリストする内部結合には、暗黙的な結合表記が存在します。 例：SELECT * FROM product、category WHERE product.CategoryID = category.CategoryID; | 外部結合には暗黙的な結合表記はありません。 |
内側と外側の結合vsユニオン
時々、JoinとUnionを混同しますが、これはで最もよくある質問の1つでもあります。 SQLインタビュー 。内部結合と外部結合の違いはすでに見てきました。それでは、JOINとUNIONの違いを見てみましょう。
UNIONはクエリの行を次々に配置しますが、joinはデカルト積を作成してサブセット化します。したがって、UNIONとJOINは完全に異なる操作です。
MySQLで以下の2つのクエリを実行し、その結果を確認しましょう。
UNIONクエリ：
SELECT 28 AS bah UNION SELECT 35 AS bah; 結果：
| バー | |
|---|---|
| 1 | 28 |
| 二 | 35 |
JOINクエリ：
SELECT * FROM (SELECT 38 AS bah) AS foo JOIN (SELECT 35 AS bah) AS bar ON (55=55); 結果：
| foo | バー | |
|---|---|---|
| 1 | 38 | 35 |
UNION操作は、2つ以上のクエリの結果を1つの結果セットに入れます。この結果セットは、UNIONに関連するすべてのクエリを通じて返されるすべてのレコードを保持します。したがって、基本的に、UNIONは2つの結果セットを組み合わせています。
結合操作は、これらのテーブル間の論理関係に基づいて、つまり結合条件に基づいて、2つ以上のテーブルからデータをフェッチします。結合クエリでは、あるテーブルのデータを使用して、別のテーブルのレコードを選択します。異なるテーブルに存在する同様のデータをリンクできます。
非常に簡単に理解すると、UNIONは2つのテーブルの行を結合し、結合は2つ以上のテーブルの列を結合すると言うことができます。したがって、両方を使用してn個のテーブルのデータを結合しますが、違いはデータの結合方法にあります。
以下は、UNIONとJOINの図解です。
上記は、結果セットの各レコードに両方のテーブル（テーブルAとテーブルB）の列が含まれていることを示す結合操作の図解です。この結果は、クエリで適用された結合条件に基づいて返されます。
結合は通常、非正規化（正規化の反対）の結果であり、あるテーブルの外部キーを使用して、別のテーブルの主キーを使用して列の値を検索します。
charからintへの変換c ++
上記は、結果セットの各レコードが2つのテーブルのいずれかからの行であることを示すUNION操作の図解です。したがって、UNIONの結果は、表Aと表Bの行を結合したものです。
さらに読む= >> MySQLUNIONが例を挙げて説明
結論
この記事では、 SQLの内部結合と外部結合 。また、外部結合の分類、つまり、左結合、右結合、および完全結合も確認しました。これらの結合タイプのそれぞれがどのように機能し、それらが互いにどのように異なるかを見てきました。
また、これらの結合タイプ間のパフォーマンス比較も行いました。また、結合とユニオンの違いについても説明しました。
また読む= >> MySQL結合タイプ
この記事が、さまざまな結合タイプの違いに関する疑問を解消するのに役立つことを願っています。これにより、目的の結果セットに基づいて選択する結合タイプを決定できると確信しています。