JOIN
1 つのテーブルまたはビューを指定することも、複数のテーブルを指定することもできます。あるいは、1 つのビューと複数のテーブルを指定することもできます。複数のテーブルを指定する場合、テーブルを結合するといいます。
構文
結合定義 ::= テーブル参照 [結合タイプ] JOIN テーブル参照 ON 検索条件
| テーブル参照 CROSS JOIN テーブル参照
| 外部結合の定義
結合タイプ ::= INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER]
外部結合の定義 ::= テーブル参照 外部結合タイプ JOIN テーブル参照 ON 検索条件
外部結合タイプ ::= LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER]
備考
次の例は、2 つのテーブルの外部結合を示しています。
SELECT * FROM Person LEFT OUTER JOIN Faculty ON Person.ID = Faculty.ID
次の例は、ベンダー文字列に埋め込まれた外部結合を示しています。"OJ" は大文字と小文字のどちらでもかまいません。
SELECT t1.deptno, ename FROM {OJ emp t2 LEFT OUTER JOIN dept t1 ON t2.deptno=t1.deptno}
PSQL は、Microsoft の ODBC ドキュメントに明記されている 2 つのテーブルの外部結合をサポートしています。単純な 2 つのテーブルの外部結合のほかにも、PSQL では n とおりのネストされた外部結合がサポートされています。
外部結合は、ベンダー文字列に埋め込んでも埋め込まなくてもかまいません。ベンダー文字列が使用された場合、PSQL ではそれをストリップし、実際の外部結合テキストを解析します。
LEFT OUTER
PSQL データベース エンジンでは、SQL92(SQL2)をモデルとして LEFT OUTER JOIN を実装しています。構文は、クロス結合、右外部結合、完全外部結合、および内部結合が組み込まれた全 SQL92 構文のサブセットです。以下の TableRefList は、SELECT ステートメント内の FROM キーワードと、後続の WHERE、HAVING、およびその他の句の間に置かれます。TableRef と LeftOuterJoin の再帰的な性質に注意してください。TableRef が TableRef を含む左外部結合で、その TableRef も左外部結合で、というように続けることができます。
TableRefList :
TableRef [, TableRefList]
| TableRef
| OuterJoinVendorString [, TableRefList]
TableRef :
TableName [CorrelationName]
| LeftOuterJoin
| (LeftOuterJoin )
LeftOuterJoin :
TableRef LEFT OUTER JOIN TableRef ON SearchCond
検索条件(SearchCond)には結合条件が含まれます。この条件の通常の形式は、LT.ColumnName = RT.ColumnName のようになります。ここで、LT は左テーブル、RT は右テーブル、ColumnName は指定されたドメイン内の列を表します。検索条件内の各述部には、非リテラル式が含まれている必要があります。
左外部結合の実装は、Microsoft の ODBC ドキュメントに示されている構文の範囲を超えています。
ベンダー文字列
前のセクションの構文は、Microsoft ODBC ドキュメントに記載されていますが、詳しい説明は含まれていません。さらに、左外部結合の最初と最後のベンダー文字列エスケープ シーケンスによって外部結合のコア構文は変わりません。
PSQL データベース エンジンでは、ベンダー文字列を含まない外部結合構文を受け入れます。ただし、複数データベースにわたる ODBC 準拠を必要とするアプリケーションでは、ベンダーの文字列構成を使用する必要があります。ODBC のベンダー文字列外部結合では 3 つ以上のテーブルはサポートされないため、以下の表
31 に記載されている構文を使用しなければならない場合があります。
例
このセクションの例では、次の 4 つのテーブルを使用します。
表 28 Emp テーブル
FirstName | LastName | DeptID | EmpID |
Franky | Avalon | D103 | E1 |
Gordon | Lightfoot | D102 | E2 |
Lawrence | Welk | D101 | E3 |
Bruce | Cockburn | D102 | E4 |
表 29 Dept テーブル
DeptID | LocID | Name |
D101 | L1 | TV |
D102 | L2 | Folk |
表 30 Addr テーブル
EmpID | Street |
E1 | 101 Mem Lane |
E2 | 14 Young St. |
表 31 Loc テーブル
LocID | Name |
L1 | PlanetX |
L2 | PlanetY |
以下の例は、単純な 2 方向左外部結合を示しています。
SELECT * FROM Emp LEFT OUTER JOIN Dept ON Emp.DeptID = Dept.DeptID
この単純な 2 方向外部結合では、次の結果セットが作成されます。
表 32 2 方向左外部結合
Emp | | | | Dept | | |
FirstName | LastName | DeptID | EmpID | DeptID | LocID | Name |
Franky | Avalon | D103 | E1 | NULL | NULL | NULL |
Gordon | Lightfoot | D102 | E2 | D102 | L2 | Folk |
Lawrence | Welk | D101 | E3 | D101 | L1 | TV |
Bruce | Cockburn | D102 | E4 | D102 | L2 | Folk |
テーブル内の Franky Avalon の NULL エントリに注目してください。これは、D103 の DeptID が Dept テーブル内に見つからなかったためです。標準(INNER)結合では、Franky Avalon はまとめて結合セットから削除されてしまうでしょう。
アルゴリズム
前の例で PSQL エンジンが使用しているアルゴリズムは、次のようになります。
左テーブルを取り出して、右テーブルを走査します。現行の右テーブル行で ON 条件が TRUE になる場合ごとに、現行の左テーブル行に追加される適合した右テーブル行から成る結果セット行を返します。
ON 条件が TRUE の右テーブル行がない(現行の左テーブル行に対し、すべての右テーブル行で ON 条件が FALSE になる)場合は、すべての列の値を NULL にして右テーブルの行インスタンスを作成します。
その結果セットは、行ごとに現行の左テーブル行と結合され、返される結果セット内でインデックス付けされます。左テーブル行ごとにこのアルゴリズムが繰り返されて、完全な結果セットが構築されます。前述の単純な 2 方向左外部結合では、Emp が左テーブルで Dept が右テーブルです。
メモ: アルゴリズムとは関係ありませんが、左テーブルを右テーブルに追加する場合、クエリの選択リスト内に指定されたように適切に射影が行われることが前提となります。この射影の範囲は、すべての列(たとえば、SELECT * FROM ...)から、結果セット内の 1 つの列(たとえば、SELECT FirstName FROM...)です。
============
放射状左外部結合では、1 つの中央テーブルにほかのすべてのテーブルが結合されます。以下の 3 方向の放射状左外部結合の例では、Emp が中央テーブルで、すべての結合はそのテーブルから射影したものです。
SELECT * FROM (Emp LEFT OUTER JOIN Dept ON Emp.DeptID = Dept.DeptID) LEFT OUTER JOIN Addr ON Emp.EmpID = Addr.EmpID
表 33 3 方向の放射状左外部結合
Emp | Dept | Addr |
First Name | Last Name | Dept ID | Emp ID | Dept ID | Loc ID | Name | Emp ID | Street |
Franky | Avalon | D103 | E1 | NULL | NULL | NULL | E1 | 101 Mem Lane |
Gordon | Lightfoot | D102 | E2 | D102 | L2 | Folk | E2 | 14 Young St |
Lawrence | Welk | D101 | E3 | D101 | L1 | TV | NULL | NULL |
Bruce | Cockburn | D102 | E4 | D101 | L1 | TV | NULL | NULL |
============
チェーン状の左外部結合では、1 つのテーブルが別のテーブルに結合され、そのテーブルがまた別のテーブルに結合されます。次の例は、3 方向のチェーン状左外部結合を示しています。
SELECT * FROM (Emp LEFT OUTER JOIN Dept ON Emp.DeptID = Dept.DeptID) LEFT OUTER JOIN Loc ON Dept.LocID = Loc.LocID
表 34 3 方向のチェーン状左外部結合
Emp | | | | Dept | | | Loc | |
First Name | Last Name | Dept ID | Emp ID | Dept ID | Loc ID | Name | Loc ID | Name |
Franky | Avalon | D103 | E1 | NULL | NULL | NULL | NULL | NULL |
Gordon | Lightfoot | D102 | E2 | D102 | L2 | Folk | L2 | PlanetY |
Lawrence | Welk | D101 | E3 | D101 | L1 | TV | L1 | PlanetX |
Bruce | Cockburn | D102 | E4 | D101 | L1 | TV | L1 | PlanetX |
この結合は、次のように表すこともできます。
SELECT * FROM Emp LEFT OUTER JOIN (Dept LEFT OUTER JOIN Loc ON Dept.LocID = Loc.LocID) ON Emp.DeptID = Dept.DeptID
最初の構文は放射状結合とチェーン状結合の両方に使用できるため、最初の構文をお勧めします。ネストされた左外部結合 ON 条件はネスト外部のテーブル内の列を参照できないため、この 2 番目の構文は放射状結合には使用できません。つまり、次のクエリでは Emp.EmpID への参照は不正です。
SELECT * FROM Emp LEFT OUTER JOIN (Dept LEFT OUTER JOIN Addr ON Emp.EmpID = Addr.EmpID) ON Emp.DeptID = Dept.DeptID
============
次の例は、最適化が足りない 3 方向の放射状左外部結合を示しています。
SELECT * FROM Emp E1 LEFT OUTER JOIN Dept ON E1.DeptID = Dept.DeptID, Emp E2 LEFT OUTER JOIN Addr ON E2.EmpID = Addr.EmpID WHERE E1.EmpID = E2.EmpID
表 35 最適化が足りない 3 方向の放射状左外部結合
Emp | | | | Dept | | | Addr | |
First Name | Last Name | Dept ID | Emp ID | Dept ID | Loc ID | Name | Emp ID | Street |
Franky | Avalon | D103 | E1 | NULL | NULL | NULL | E1 | 101 Mem Lane |
Gordon | Lightfoot | D102 | E2 | D102 | L2 | Folk | E2 | 14 Young St |
Lawrence | Welk | D101 | E3 | D101 | L1 | TV | NULL | NULL |
Bruce | Cockburn | D102 | E4 | D101 | L1 | TV | NULL | NULL |
Emp 内の EmpID に
NULL 値がなく、EmpID が重複値のない列である場合、このクエリでは、表
34 の結果と同じ結果が返されます。ただし、このクエリは表
34 で示される結果ほど最適化されておらず、はるかに遅くなる可能性があります。
関連項目