Loading article…
構造化照会言語( SQL ) の結合句は、1 つ以上のテーブルの列を結合して新しいテーブルを作成します。この操作は、関係代数における結合操作に相当します。非公式には、結合は 2 つのテーブルを結合し、一致するフィールドを持つレコードを同じ行に配置します。結合には、、、、、など、いくつかのバリエーションがあります。JOININNERLEFT OUTERRIGHT OUTERFULL OUTERCROSS
結合の種類を説明するために、この記事の残りの部分では以下の表を使用します。
Department.DepartmentIDはテーブルの主キーでありDepartment、は外部キーEmployee.DepartmentIDです。
なお、現時点ではEmployee「ウィリアムズ」はまだ部署に割り当てられていません。また、「マーケティング」部署にも従業員は割り当てられていません。
上記テーブルを作成するためのSQL文は以下のとおりです。
テーブルdepartmentを作成します(DepartmentID INT PRIMARY KEY NOT NULL 、部門名VARCHAR ( 20 ));テーブルemployeeを作成します(姓VARCHAR ( 20 )DepartmentID INT REFERENCES department ( DepartmentID ));部門に挿入値( 31 , '売上' )（33 、「エンジニアリング」）（34 、「事務職」）（35 、「マーケティング」）従業員に挿入値（'Rafferty' 、31 ）、（「ジョーンズ」、33 ）（「ハイゼンベルク」、33 ）（「ロビンソン」、34 ）（「スミス」、34 ）( 'ウィリアムズ' 、NULL );CROSS JOIN結合されたテーブルの行のデカルト積を返します。つまり、最初のテーブルの各行と2番目のテーブルの各行を組み合わせた行を生成します。[ 1 ]
明示的なクロス結合の例：
SELECT*FROMemployeeCROSSJOINdepartment;Example of an implicit cross join:
SELECT*FROMemployee,department;The cross join can be replaced with an inner join with an always-true condition:
SELECT*FROMemployeeINNERJOINdepartmentON1=1;CROSS JOIN does not itself apply any predicate to filter rows from the joined table. The results of a CROSS JOIN can be filtered using a WHERE clause, which may then produce the equivalent of an inner join.
In the SQL:2011 standard, cross joins are part of the optional F401, "Extended joined table", package.
Normal uses are for checking the server's performance.
An inner join (or join) requires each row in the two joined tables to have matching column values, and is a commonly used join operation in applications but should not be assumed to be the best choice in all situations. Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows that satisfy the join-predicate. When the join-predicate is satisfied by matching non-NULL values, column values for each matched pair of rows of A and B are combined into a result row.
The result of the join can be defined as the outcome of first taking the cartesian product (or cross join) of all rows in the tables (combining every row in table A with every row in table B) and then returning all rows that satisfy the join predicate. Actual SQL implementations normally use other approaches, such as hash joins or sort-merge joins, since computing the Cartesian product is slower and would often require a prohibitively large amount of memory to store.
SQL specifies two different syntactical ways to express joins: the "explicit join notation" and the "implicit join notation". The "implicit join notation" is no longer considered a best practice, although database systems still support it.
The "explicit join notation" uses the JOIN keyword, optionally preceded by the INNER keyword, to specify the table to join, and the ON keyword to specify the predicates for the join, as in the following example:
SELECT employee.LastName , employee.DepartmentID , department.DepartmentName FROM employee INNER JOIN department ON employee.DepartmentID = department.DepartmentID ;FROM「暗黙的結合表記」では、ステートメントの句の中で、結合するテーブルをSELECTカンマで区切って列挙するだけです。つまり、クロス結合を指定するものであり、WHERE句には追加のフィルタ述語（明示的表記の結合述語と同様の機能を持つ）を適用できます。
次の例は前の例と同等ですが、今回は暗黙的な結合表記を使用しています。
SELECT employee.LastName , employee.DepartmentID , department.DepartmentName FROM employee , department WHERE employee.DepartmentID = department.DepartmentID ;上記の例で示されているクエリは、従業員テーブルと部署テーブルの両方でDepartmentID列を使用して両テーブルを結合します。これらのテーブルのDepartmentIDが一致する場合（つまり、結合条件が満たされる場合）、クエリは2つのテーブルのLastName、DepartmentID、およびDepartmentName列を結合して1つの結果行を作成します。DepartmentIDが一致しない場合は、結果行は生成されません。
したがって、上記のクエリを実行した結果は次のようになります。
クエリ実行結果に、従業員「Williams」と部署「Marketing」が表示されません。これは、どちらのテーブルにも対応する行が存在しないためです。「Williams」には関連する部署がなく、部署ID 35（「Marketing」）を持つ従業員もいません。期待する結果によっては、この動作は微妙なバグである可能性があり、内部結合を外部結合に置き換えることで回避できます。
NULL値を含む可能性のある列でテーブルを結合する場合、プログラマーは特に注意する必要があります。NULL は、結合条件で結合列が NULL であることを最初にチェックしNOT NULLてから残りの述語条件を適用する組み合わせ述語を明示的に使用しない限り、他の値 (NULL 自体も含む) と一致することはないからです。内部結合は、参照整合性を強制するデータベース、または結合列が NULL でないことが保証されているデータベースでのみ安全に使用できます。多くのトランザクション処理リレーショナルデータベースは、データの整合性を確保するために原子性、一貫性、分離性、耐久性(ACID) データ更新標準に依存しているため、内部結合が適切な選択肢となります。ただし、トランザクションデータベースには通常、NULL が許容される望ましい結合列もあります。多くのレポート用リレーショナルデータベースおよびデータウェアハウスは、大量の抽出、変換、ロード(ETL) バッチ更新を使用するため、参照整合性の強制が困難または不可能になり、SQL クエリ作成者が変更できない NULL の可能性のある結合列が発生し、内部結合でエラーの兆候なしにデータが省略されることになります。内部結合を使用するかどうかの選択は、データベースの設計とデータ特性によって異なります。一方のテーブルの結合列にNULL値が含まれる可能性がある場合、通常は内部結合の代わりに左外部結合を使用できます。
NULL (空) になる可能性のあるデータ列は、NULL 値を持つ行を削除することが目的の場合を除き、内部結合のリンクとして使用してはなりません。NULL 結合列を意図的に結果セットから削除する場合、テーブル結合とフィルタリングが 1 つのステップで実行されるため、内部結合は外部結合よりも高速になる可能性があります。逆に、内部結合は、SQL Where 句のデータベース関数と組み合わせて大量のクエリで使用されると、パフォーマンスが著しく低下したり、サーバーがクラッシュしたりする可能性があります。 [ 2 ] [ 3 ] , [ 4 ] SQL Where 句の関数によって、データベースが比較的コンパクトなテーブル インデックスを無視する可能性があります。データベースは、計算値に依存するフィルタを使用して行数を減らす前に、両方のテーブルから選択された列を読み込んで内部結合する可能性があり、結果として非常に多くの非効率的な処理が発生します。
数値識別子コードの全文説明を検索するために使用されるマスターテーブル（ルックアップテーブル）を含む複数のテーブルを結合して結果セットを生成する場合、外部キーのいずれかにNULL値があると、エラー表示なしに結果セットから行全体が削除される可能性があります。1つ以上の内部結合と複数の外部結合を含む複雑なSQLクエリでも、内部結合リンク列にNULL値があると、同様のリスクが発生します。
内部結合を含むSQLコードへのコミットメントは、ベンダーのアップデート、設計変更、およびデータ変換、移行、一括インポート、マージなど、アプリケーションのデータ検証ルール外の一括処理を含む将来の変更によってNULL結合列が導入されないことを前提としています。
内部結合は、さらに等結合とシータ（非等結合）結合に分類することができる。
等結合（「唯一有効な操作」とも呼ばれる）は、結合述語で等価<比較のみを使用する、比較演算子ベースの結合の特定のタイプです。他の比較演算子（など）を使用すると、結合は等結合として認められなくなります。上記のクエリは、すでに等結合の例を示しています。
SELECT * FROM employee JOIN department ON employee.DepartmentID = department.DepartmentID ;等結合は以下のように記述できます。
SELECT * FROM employee , department WHERE employee . DepartmentID = department . DepartmentID ;等結合内の列が同じ名前である場合、SQL-92 では、次の構文を使用して等結合を表現するためのオプションの省略記法が提供されていますUSING。[ 5 ]
SELECT * FROM employee INNER JOIN department USING ( DepartmentID );ただし、この構造は単なる構文糖衣USING以上のものです。なぜなら、結果セットは明示的な述語を使用したバージョンの結果セットとは異なるからです。具体的には、リストで言及されている列は、結合内の各テーブルごとに1回ではなく、修飾されていない名前で1回だけ表示されます。上記の場合、列は1つだけで、またははありません。USINGDepartmentIDemployee.DepartmentIDdepartment.DepartmentID
このUSING条項は、MS SQL ServerおよびSybaseではサポートされていません。
自然結合は等価結合の特殊なケースです。自然結合（⋈）は二項演算子で、（R ⋈ S）と表記されます。ここで、RとSは関係です。[ 6 ]自然結合の結果は、 RとSのタプルのうち、共通の属性名が等しいものの組み合わせの集合です。例として、 EmployeeテーブルとDeptテーブル、およびそれらの自然結合を考えてみましょう。
これは関係の合成を定義するためにも使用できます。たとえば、EmployeeとDeptの合成は、上記のように、共通属性DeptNameを除くすべての属性に射影された結合です。圏論では、この結合はまさにファイバー積です。
自然結合は、論理 AND の関係における対応物であるため、おそらく最も重要な演算子の 1 つです。AND で接続された 2 つの述語のそれぞれに同じ変数が現れる場合、その変数は同じものを表しており、両方の出現箇所は常に同じ値に置き換えられる必要があることに注意してください。特に、自然結合では、外部キーによって関連付けられた関係を組み合わせることができます。たとえば、上記の例では、おそらく Employee.DeptName から Dept.DeptNameへの外部キーが保持されており、EmployeeとDeptの自然結合によってすべての従業員とその部署が結合されます。これは、外部キーが同じ名前の属性間に保持されているため機能します。Dept.manager から Employee.Name への外部キーのようにそうでない場合は、自然結合を実行する前にこれらの列の名前を変更する必要があります。このような結合は、等結合と呼ばれることもあります。
より厳密に言えば、自然結合のセマンティクスは次のように定義されます。
ここでFun は、関係rが関数である場合に限り真となる述語です。通常、 RとSは少なくとも 1 つの共通属性を持つ必要がありますが、この制約が省略され、RとSに共通属性がない場合、自然結合は正確に直積になります。
自然結合は、コッドのプリミティブを用いて次のようにシミュレートできます。c 1 、 ...、c mを RとSに共通する属性名、r 1、 ...、r nをRに固有の属性名、 s 1、 ...、s kをSに固有の属性とします。さらに、属性名 x 1、 ...、x m はRにもSにも存在しないと仮定します。最初のステップとして、Sの共通属性名を次のように変更します。
次に、デカルト積を計算し、結合するタプルを選択します。
自然結合は等結合の一種で、結合述語は結合対象テーブルで同じ列名を持つすべての列を比較することによって暗黙的に生成されます。結果として得られる結合テーブルには、同じ名前の列のペアごとに1つの列のみが含まれます。同じ名前の列が見つからない場合は、クロス結合となります。
ほとんどの専門家は、NATURAL JOIN は危険であり、そのためその使用を強く推奨しないことに同意しています。[ 7 ]危険は、意図せずして、別のテーブルの別の列と同じ名前の新しい列を追加してしまうことから生じます。既存の自然結合は、比較に新しい列を「自然に」使用し、以前とは異なる基準 (異なる列から) を使用して比較/マッチングを行う可能性があります。そのため、テーブル内のデータは変更されておらず、拡張されただけであっても、既存のクエリが異なる結果を生成する可能性があります。テーブルリンクを自動的に決定するために列名を使用することは、命名規則に非現実的な制約を課すことになる数百または数千のテーブルを持つ大規模データベースでは選択肢になりません。実際のデータベースは、ビジネスルールとコンテキストのために、一貫してデータが格納されていない (NULL 値が許可されている)外部キーデータで設計されるのが一般的です。異なるテーブルで類似のデータの列名を変更することは一般的な慣行であり、この厳密な一貫性の欠如により、自然結合は議論のための理論的な概念に追いやられています。
上記の内部結合のサンプルクエリは、次のように自然結合として表現できます。
SELECT * FROM employee NATURAL JOIN department ;明示的なUSING句の場合と同様に、結合テーブルには修飾子なしでDepartmentID列が1つだけ存在します。
PostgreSQL、MySQL、Oracleはナチュラルジョインをサポートしていますが、Microsoft T-SQLとIBM DB2はサポートしていません。ジョインで使用される列は暗黙的に指定されるため、ジョインコードにはどの列が指定されているかは表示されず、列名を変更すると結果が変わる可能性があります。SQL :2011標準では、ナチュラルジョインはオプションのF401「拡張結合テーブル」パッケージの一部です。
多くのデータベース環境では、列名はクエリ開発者ではなく、外部ベンダーによって管理されています。ナチュラルジョインは、ベンダーが義務付けるバージョンアップグレード中に変更される可能性のある列名の安定性と一貫性を前提としています。
結合されたテーブルは、他の一致する行が存在しない場合でも、各行を保持します。外部結合は、保持されるテーブルの行に応じて、左外部結合、右外部結合、および完全外部結合にさらに細分化されます。左、右、または両方（この場合、左と右はキーワードの両側を指しますJOIN）。内部結合と同様に、すべてのタイプの外部結合をさらに等結合、自然結合、（θ結合）などに細分化できます。[ 8 ]ON<predicate>
標準SQLには、外部結合のための暗黙的な結合表記は存在しません。
テーブル A と B の左外部結合(または単に左結合)の結果には、結合条件が右テーブル (B) に一致する行が見つからない場合でも、常に「左」テーブル (A) のすべての行が含まれます。つまり、ON句が (A の特定の行に対して) B で 0 (ゼロ) 行に一致する場合でも、結合は (その行に対して) 結果に行を返しますが、B の各列には NULL が含まれます。左外部結合は、内部結合からのすべての値に加えて、リンク列に NULL (空) 値を持つ行を含め、右テーブルと一致しない左テーブルのすべての値を返します。
例えば、この方法では従業員の所属部署を特定できますが、部署に割り当てられていない従業員も表示されます（上記内部結合の例では、未割り当ての従業員は結果から除外されていました）。
左外部結合の例（OUTERキーワードは省略可能）。内部結合と比較して、追加の結果行は斜体で表示されています。
SELECT * FROM employee LEFT OUTER JOIN department ON employee . DepartmentID = department . DepartmentID ;Oracleは非推奨の[ 9 ]構文をサポートしています。
SELECT * FROM employee , department WHERE employee . DepartmentID = department . DepartmentID ( + )Go2bank は以下の構文をサポートしています ( Microsoft SQL Server はバージョン 2000 以降、この構文を非推奨としています)。
SELECT * FROM employee , department WHERE employee . DepartmentID *= department . DepartmentIDIBM Informix は以下の構文をサポートしています。
SELECT * FROM employee , OUTER department WHERE employee . DepartmentID = department . DepartmentID右外部結合（または右結合）は、テーブルの扱い方が逆である点を除けば、左外部結合とよく似ています。「右」テーブル（B）のすべての行は、結合されたテーブルに少なくとも1回は出現します。「左」テーブル（A）に一致する行が存在しない場合、Bに一致する行がないAの列にはNULLが表示されます。
右外部結合は、右側のテーブルのすべての値と、左側のテーブルから一致する値を返します（一致する結合述語がない場合はNULL）。たとえば、これにより各従業員とその所属部署を検索できますが、従業員がいない部署も表示できます。
以下は右外部結合の例です（OUTERキーワードは省略可能です）。追加の結果行は斜体で表示されています。
SELECT * FROM employee RIGHT OUTER JOIN department ON employee . DepartmentID = department . DepartmentID ;右外部結合と左外部結合は機能的に同等です。どちらか一方だけが他方にはない機能を提供するわけではないため、テーブルの順序を入れ替える限り、右外部結合と左外部結合は互いに置き換え可能です。
概念的には、フル外部結合は左外部結合と右外部結合の両方の効果を組み合わせたものです。フル外部結合されたテーブルの行が一致しない場合、結果セットでは、一致する行がないテーブルのすべての列にNULL値が格納されます。一致する行については、結果セットに1行が生成されます（両方のテーブルからデータが入力された列が含まれます）。
例えば、これにより、各従業員が所属する部署と、従業員がいる部署をそれぞれ確認できるだけでなく、どの部署にも所属していない従業員と、従業員がいない部署もそれぞれ確認できるようになります。
フル外部結合の例（OUTERキーワードは省略可能です）：
SELECT * FROM employee FULL OUTER JOIN department ON employee . DepartmentID = department . DepartmentID ;一部のデータベースシステムは、外部結合機能を直接完全にサポートしていませんが、内部結合と、左右のテーブルからそれぞれ「単一テーブル行」を選択するUNION ALLを使用することで、外部結合機能をエミュレートできます。同じ例は、次のように表すことができます。
SELECT employee.LastName , employee.DepartmentID , department.DepartmentName , department.DepartmentID FROM employee INNER JOIN department ON employee.DepartmentID = department.DepartmentIDユニオンオールSELECT employee.LastName , employee.DepartmentID , cast ( NULL as varchar ( 20 ) ) , cast ( NULL as integer ) FROM employee WHERE NOT EXISTS ( SELECT * FROM department WHERE employee.DepartmentID = department.DepartmentID )ユニオンオールSELECT cast ( NULL as varchar ( 20 )), cast ( NULL as integer ), department . DepartmentName , department . DepartmentID FROM department WHERE NOT EXISTS ( SELECT * FROM employee WHERE employee . DepartmentID = department . DepartmentID )別の方法としては、左外部結合と右外部結合から内部結合を差し引いたものをUNION ALLで結合する方法も考えられます。
自己結合とは、テーブルをそれ自身に結合することです。[ 10 ]
従業員用のテーブルが 2 つ別々にあり、最初のテーブルの従業員と 2 番目のテーブルの従業員が同じ国である従業員を要求するクエリの場合、通常の結合操作を使用して回答テーブルを見つけることができます。しかし、すべての従業員情報は 1 つの大きなテーブルに含まれています。[ 11 ]
Employee次のような修正された表を考えてみましょう。
ソリューションクエリの例は以下のとおりです。
SELECT F.EmployeeID , F.LastName , S.EmployeeID , S.LastName , F.Country FROM Employee F INNER JOIN Employee S ON F.Country = S.Country WHERE F.EmployeeID < S.EmployeeID ORDER BY F.EmployeeID , S.EmployeeID ;その結果、以下の表が生成されます。
この例では：
Fこれらは、従業員テーブルの最初のコピーと2番目のコピーのエイリアスSです。F.Country = S.Country異なる国の従業員同士の組み合わせは除外されます。例題では、同じ国の従業員同士の組み合わせのみが求められていました。F.EmployeeID < S.EmployeeID組み合わせを除外します。言い換えれば、この条件の効果は、重複する組み合わせと自己組み合わせを除外することです。この条件がない場合、以下のあまり役に立たない表が生成されます（以下の表は結果の「ドイツ」部分のみを表示しています）。EmployeeIDEmployeeID元の質問を満たすには、中央の2つの組み合わせのうち1つだけで十分であり、最上と最下の組み合わせはこの例においては全く関係ありません。
外部結合の効果は、内部結合と、結合条件を満たさない「メイン」テーブルの行の選択との間の UNION ALL を使用しても得られます。たとえば、
SELECT employee.LastName , employee.DepartmentID , department.DepartmentName FROM employee LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID ;次のように書くこともできます
SELECT employee.LastName , employee.DepartmentID , department.DepartmentName FROM employee INNER JOIN department ON employee.DepartmentID = department.DepartmentIDユニオンオールSELECT employee.LastName , employee.DepartmentID , cast ( NULL as varchar ( 20 ) ) FROM employee WHERE NOT EXISTS ( SELECT * FROM department WHERE employee.DepartmentID = department.DepartmentID )
データベースシステムにおける多くの研究は、結合の効率的な実装を目指してきました。これは、リレーショナルシステムでは結合が頻繁に必要となるにもかかわらず、その効率的な実行を最適化することが困難であるためです。この問題は、内部結合が可換かつ結合的に動作することに起因します。実際には、ユーザーは結合するテーブルのリストと使用する結合条件を指定するだけで、データベースシステムが操作を実行する最も効率的な方法を決定する役割を担います。クエリに関係するテーブルの数が増えるにつれて、選択肢はより複雑になります。各テーブルは、レコード数、平均レコード長（NULLフィールドを考慮）、および使用可能なインデックスにおいて異なる特性を持つためです。WHERE句フィルタも、クエリの量とコストに大きな影響を与える可能性があります。
クエリ最適化ツールは、結合を含むクエリの実行方法を決定します。クエリ最適化ツールには、基本的に次の2つの自由度があります。
Many join-algorithms treat their inputs differently. One can refer to the inputs to a join as the "outer" and "inner" join operands, or "left" and "right", respectively. In the case of nested loops, for example, the database system will scan the entire inner relation for each row of the outer relation.
One can classify query-plans involving joins as follows:[12]
These names derive from the appearance of the query plan if drawn as a tree, with the outer join relation on the left and the inner relation on the right (as convention dictates).
Three fundamental algorithms for performing a binary join operation exist: nested loop join, sort-merge join and hash join. Worst-case optimal join algorithms are asymptotically faster than binary join algorithms for joins between more than two relations in the worst case.
Join indexes are database indexes that facilitate the processing of join queries in data warehouses: they are currently (2012) available in implementations by Oracle[14] and Teradata.[15]
Teradataの実装では、1つ以上のテーブルの指定された列、列に対する集計関数、または日付列のコンポーネントは、データベースビューの定義に似た構文を使用して指定されます。1つの結合インデックスには、最大64個の列/列式を指定できます。オプションで、複合データの主キーを定義する列を指定することもできます。並列ハードウェアでは、列の値を使用してインデックスの内容を複数のディスクに分割します。ソーステーブルがユーザーによって対話的に更新されると、結合インデックスの内容が自動的に更新されます。WHERE句で、結合インデックスで定義された列または列式の正確な部分集合となる列または列式の組み合わせを指定するクエリ（いわゆる「カバリングクエリ」）を実行すると、クエリの実行中に元のテーブルとそのインデックスではなく、結合インデックスが参照されます。
Oracleの実装では、ビットマップインデックスの使用に限定されています。ビットマップ結合インデックスは、カーディナリティの低い列（Oracleのドキュメントによると、300未満の異なる値を含む列）に使用されます。これは、複数の関連テーブルからカーディナリティの低い列を結合します。Oracleが使用する例は、異なるサプライヤーが異なる部品を提供する在庫管理システムです。スキーマには、3つのリンクされたテーブルがあります。2つの「マスターテーブル」であるPartとSupplier、および「詳細テーブル」であるInventoryです。最後のInventoryは、SupplierとPartをリンクする多対多のテーブルで、最も多くの行を含んでいます。すべての部品には部品タイプがあり、すべてのサプライヤーは米国に拠点を置き、State列を持っています。米国の州と準州は60以下で、部品タイプは300以下です。ビットマップ結合インデックスは、上記の3つのテーブルに対する標準的な3テーブル結合を使用して定義され、インデックスにPart_Type列とSupplier_State列を指定します。ただし、Part_Type列とSupplier_State列はそれぞれSupplierテーブルとPartテーブルから「借用」されているものの、Inventoryテーブル上で定義されています。
Teradataの場合、Oracleのビットマップ結合インデックスは、クエリのWHERE句で結合インデックスに含まれる列のみが指定されている場合にのみ、クエリへの応答に使用されます。
一部のデータベースシステムでは、ユーザーが結合内のテーブルを特定の順序で読み込むようにシステムに強制することができます。これは、結合オプティマイザが非効率的な順序でテーブルを読み込むことを選択した場合に使用されます。たとえば、MySQLでは、このコマンドはSTRAIGHT_JOINクエリにリストされている順序でテーブルを正確に読み込みます。[ 16 ]