2 つのテーブルのデータを基にして、テーブルを作成します。
Synopsis
[INNER | LEFT [OUTER] | RIGHT [OUTER] ] JOIN
table [[AS] identifier] ON condition-expression
CROSS JOIN table [[AS] identifier]
NATURAL [INNER | LEFT [OUTER] | RIGHT [OUTER] ] JOIN
table [[AS] identifier]
[INNER | LEFT [OUTER] | RIGHT [OUTER] ] JOIN
table [[AS] identifier] USING (identifier-commalist)
JOIN は、2 つのテーブルを組み合わせて、オプションの制限条件に従う結合テーブルを作成します。新規のテーブルのすべての行は、制限された条件を満たしている必要があります。JOIN は、テーブルのデータを別のテーブルのデータと結合する手段を提供し、レポートやクエリの定義に頻繁に使用されます。
JOIN 式は
SELECT 文で
FROM 節の一部として使用されます。FROM 節の JOIN 式には複数の JOIN を含めることができます。キーワードの %INORDER および %FULL を FROM キーワードの直後に使用して、複数の JOIN を最適化することができます。これらのキーワードの詳細は、"
FROM" 節を参照してください。
Caché は、
JOIN の複数の異なる構文形式をサポートします。しかし、これら複数の公式化は以下の 4 つの結合のタイプに参照します。
CROSS JOIN は最初のテーブルの行すべてを次のテーブルの行すべてと交差させる結合です。結果は、多くの重複データを持つ、大きな、論理的に総合的なテーブル (デカルト積) になります。通常、この結合はコンマで区切られたテーブルのリストを FROM 節内で指定し、その後 WHERE 節を使用して制限条件を指定することによって実行されます。
INNER JOIN は、最初のテーブルの行を次のテーブルの行とリンクする結合です。最初のテーブルの行で、次のテーブル内に対応する行が見つからないものはリンクしません。結果は、各フィールドが一意のデータを含む、小さなテーブルになります。
LEFT OUTER JOIN と RIGHT OUTER JOIN は機能上ほとんどの点で同一であり (構文が逆)、そのため、集合的に
単方向外部結合とみなされる場合が多くあります。単方向外部結合は、最初のテーブル (ソース・テーブル) のすべての行を次のテーブルの行とリンクさせる結合です。最初のテーブルの行に一致する行が次のテーブルで見つからない場合でも、リンクは行われます。結果はテーブルで、最初のテーブル (ソース・テーブル) 内にあるフィールドの中には、NULL データと組み合わされるものもあります。
単方向外部結合を指定する場合、FROM 節のテーブルを指名する順番が非常に重要です。LEFT OUTER JOIN では、最初に指定するテーブルが結合のソース・テーブルです。RIGHT OUTER JOIN では、2 つ目に指定するテーブルが結合のソース・テーブルです。この 2 つのタイプの単方向外部結合にはわずかに異なる制限があります。
NATURAL JOIN は、INNER JOIN、LEFT OUTER JOIN、または RIGHT OUTER JOIN の前に NATURAL キーワードを付けたものです。JOIN の前に NATURAL という語を付けると、2 つのテーブルで同じ名前を持つすべての列において結合が実行されます。NATURAL JOIN では、同じ名前を持つすべての列で自動的に等値条件が実行されるため、ON 節や USING 節を指定することはできません。
NATURAL JOIN のどちらのオペランドでも、単純なベース・テーブル参照 (ビューやサブクエリではない) のみがサポートされます。
NATURAL JOIN は、JOIN 式内の最初の JOIN としてのみ指定することができます。
INNER JOIN、LEFT OUTER JOIN、または RIGHT OUTER JOIN には ON 節を指定できます。ON 節のある JOIN のどちらのオペランドでも、テーブル、ビュー、およびサブクエリがサポートされます (以下で説明する単方向外部結合の制限事項に従います)。ON 節のある JOIN は、JOIN 式内のどこでも指定することができます。ON 節のある JOIN に NATURAL キーワードを接頭語として付けたり、USING 節を指定することはできません。
INNER JOIN では、ON 節の条件式に制限はなく、どのような式も有効です。これには、より大きい (>) やより小さい (<) などの算術演算子や、OR や NOT などの論理演算子も含まれます。また、括弧を使用して INNER JOIN の条件式をグループ化したり、構文的に意味のあるものにすることもできます。
LEFT OUTER JOIN と RIGHT OUTER JOIN には、以下に説明するさまざまな制限事項が適用されます。
INNER JOIN、LEFT OUTER JOIN、または RIGHT OUTER JOIN には USING 節を指定できます。USING 節のある JOIN のどちらのオペランドでも、単純なベース・テーブル参照 (ビューやサブクエリではない) のみがサポートされます。USING 節のある JOIN は、JOIN 式内の最初の JOIN としてのみ指定できます。USING 節のある JOIN に NATURAL キーワードを接頭語として付けたり、ON 節を指定することはできません。
USING 節では、1 つ以上の列名をコンマで区切ったリストを括弧で囲みます。重複する列名は無視されます。括弧は必須です。
Caché は、単方向外部結合の LEFT OUTER JOIN および RIGHT OUTER JOIN をサポートします。双方向外部結合はサポートしません。
標準の "内部" 結合では、あるテーブルの行が別のテーブルの行にリンクしている場合、後者のテーブルの行と対応しない前者のテーブルの行は、出力テーブルから除外されます。
単方向外部結合では、すべての行が一致しなくても、テーブルの行はすべて出力テーブルに含まれます。前者のテーブルは後者のテーブルから該当する情報を引き出しますが、後者のテーブルに一致する行がなくても、自分の行を犠牲にすることはありません。
例えば、クエリが Table 1 を最初にリストし、左外部結合を作成する場合、対応するレコードが Table 2 内に存在しなくても、Table 1 のすべての行を見ることができます。
単方向外部結合を指定する場合、FROM 節のテーブルを指名する順番が非常に重要です。左外部結合では、指定した最初のテーブルは結合のソース・テーブルです。右外部結合では、指定した 2 番目のテーブルが結合のソース・テーブルです。
RIGHT OUTER JOIN は JOIN 式内の最初の JOIN としてのみサポートされます。
Caché には、外部結合の表示に 3 種類の形式があります。
-
ANSI 標準構文の LEFT OUTER JOIN と RIGHT OUTER JOIN。以下の例で示しているように、SQL 標準構文は WHERE 節ではなく、SELECT 文の FROM 節で外部結合を入力します。
FROM tbl1 LEFT OUTER JOIN tbl2 ON (tbl1.key = tbl2.key)
-
-
WHERE 節で A=*B などの条件を使用する、シンボル外部結合拡張構文。左外部結合は WHERE 節の = の場所で、=* などのシンボルを使用して指定されます (これは、Microsoft SQL Server や Sybase で使用される構文とは逆であることに注意してください)。右外部結合は WHERE 節の = の場所で、*= などのシンボルを使用して指定されます。
Note:
シンボル外部結合構文 (=* および *=) は使用しないことを強くお勧めします。ANSI 標準構文の LEFT OUTER JOIN と RIGHT OUTER JOIN を使用してください。
3 種類の外部結合形式は互換性があり混在させることもできますが、ANSI 標準構文は ODBC と互換性のある唯一の構文である (また、最新の Microsoft 製品に移植可能である) ことから、可能な限り ANSI 標準構文を使用することを強くお勧めします。また、ANSI 標準構文では、シンボル構文で原則的に指定できない多くのオペレーションを指定することもできます。さらに、インターシステムズでは、古いシンボル外部結合構文で、新しい機能、拡張検証、オプティマイザの改善をサポートする予定はありません。
FROM 節内の ANSI 標準外部結合には、以下の制限があります。
-
RIGHT OUTER JOIN の場合、左のオペランドが結合式、ビュー、またはサブクエリでないこと。右のオペランドにはそのような制限はありません。
-
LEFT OUTER JOIN の場合、右のオペランドが結合式ではないこと。ただし、ビューとサブクエリは可能です。左のオペランドにはそのような制限はありません。
ANSI 標準外部結合では、ON 節により結合を規定する条件式の機能を実現します。外部結合の ON 節には、内部結合の ON 節よりも厳しい制限があります。外部結合には、以下の制限があります。
-
ON 節が 1 つ以上の等値条件で構成されていること。外部結合では等値条件のみが可能です。少なくとも 1 つの条件が 2 つの列の比較でなければなりません。
-
複数の等値条件を AND 論理演算子で組み合わせることができます。OR および NOT の論理演算子は使用できません。
-
コードをわかりやすくするために、複数の等値条件を括弧で囲むことができます。外部結合の ON 節の制限事項のために、このような括弧は構文的に意味を持ちません。
ON 節の条件は以下のいずれかの基準を満たしている必要があります ("A LEFT OUTER JOIN B ON 条件式" または "A RIGHT OUTER JOIN B ON 条件式" の場合を想定)。
-
-
LEFT OUTER JOIN の場合、B の列が定数式 (リテラル値またはパラメータ) と等しいこと。
-
RIGHT OUTER JOIN の場合、A の列が定数式 (リテラル値またはパラメータ) と等しいこと。
等式のオペランドは任意の順序で指定できます。例えば、ON A.field1=B.field3 は ON B.field3=A.field1 と同じであり、ON A.field1=literal は ON literal=A.field1 と同じです。
ON 節の等式の演算子に対しては、以下の列のオペランド値を使用できます。
-
ベース・テーブルの列。等式のどちら側であっても、ベース・テーブルの列の指定に適用される制限はありません。
-
ベース・テーブルの列に展開されるビューの列。このタイプのビュー列は外部結合のソース・テーブル側でのみ指定可能です。ソース・テーブルは、LEFT OUTER JOIN の左側、または RIGHT OUTER JOIN の右側です。
-
ストリーム・ビューの列。ストリーム・ビューは、DISTINCT、GROUP BY、または UNION のキーワードを含むビュー、または集約関数を使用して生成された列を持つビューです。結合テーブルのどちら側であっても、ストリーム・ビューの列の指定に適用される制限はありません。
-
ソース・テーブルの列は複数の等式で使用することができます。ソースでないテーブルの列では等式は 1 つのみ使用可能です。
WHERE 節内のシンボル外部結合構文では、以下のオペランド値を使用できます。
-
ベース・テーブルの列。外部結合のどちら側であっても、ベース・テーブルの列の指定に適用される制限はありません。
-
ベース・テーブルの列に展開されるビューの列。このタイプのビュー列は外部結合のソース・テーブル側でのみ使用可能です (ソース・テーブルは、LEFT OUTER JOIN (=*) の左側、または RIGHT OUTER JOIN (*=) の右側です)。
-
ストリーム・ビューの列。ストリーム・ビューは、DISTINCT、GROUP BY、または UNION のキーワードを含むビュー、または集約関数を使用して生成された列を持つビューです。外部結合のどちら側であっても、ストリーム・ビューの列の指定に適用される制限はありません。
単方向外部結合では、NULL の埋め込みが実行されます。ソース・テーブルの行で、マージされる列に NULL 値が存在する場合は、ソースでないテーブルの対応するフィールドに NULL 値が返されます。
これは、A のすべての行が返されることを指定します。返された A の各行では、A.x=B.y などの B 行がある場合、対応するすべての B 値も返されます。
A.x=B.y などの B 行がない場合は、NULL の埋め込みによって A 行に対するすべての B 値を NULL として返すようにします。
例えば、患者に関する情報を格納した Patient テーブルがあり、このテーブルに患者の主治医の ID コードを示すフィールド Patient.DocID が含まれるとします。データベースには主治医がいない患者のレコードがあるため、このような患者のレコードでは Patient.DocID フィールドは NULL になります。ここで、Patient テーブルと Doctor テーブルの結合を実行して、患者名と対応する担当医師名のテーブルを生成します。
SELECT Patient.PName,Doctor.DName
FROM Patient INNER JOIN Doctor
ON Patient.DocID=Doctor.DocID
INNER JOIN では、NULL の埋め込みは実行されません。そのため、対応する担当医師名のない患者名は返されません。
単方向外部結合では、NULL の埋め込みが実行されます。そのため、対応する担当医師名のない患者名では、Doctor.DName に NULL が返されます。
SELECT Patient.PName,Doctor.DName
FROM Patient LEFT OUTER JOIN Doctor
ON Patient.DocID=Doctor.DocID
必要な NULL の埋め込みも含んだ単方向外部結合条件は、他の条件より前に適用されます。したがって、NULL が埋め込まれた値では満たされない
WHERE 節の条件 (例えば、B のフィールド上の値域条件や等値条件) は、A と B の単方向外部結合を標準結合 (内部結合) に効果的に変換します。
例えば、"Where Doctor.Age < 45" という句を、上記の 2 つの "Paｔient" テーブル・クエリに追加する場合、これらは同じになります。ただし、"WHERE Doctor.Age < 45 OR Doctor.Age IS NULL" という句を追加する場合は、2 つのクエリにおける相違は変わりません。
通常 Caché は、括弧が使用されていない限り、内部結合と外部結合の混合したすべての構文を任意の順序でサポートします。外部結合の ON 節内の条件には、いくつかの制約があります。例えば、左外部結合では、これらの条件は A=B か X=B の形式にする必要があります。A は左のテーブルのフィールド、B は右のテーブルのフィールド、X は定数かホスト変数です。
以下の例は、Table 1 と Table 2 で実行される
JOIN 操作の結果を示しています。
Table1 Table2 Column1 Column2 Column1 Column3 aaa bbb ggg hhh ccc ccc xxx zzz xxx yyy hhh zzz
SELECT * FROM Table1 CROSS JOIN Table2
Column1 Column2 Column1 Column3 aaa bbb ggg hhh aaa bbb xxx zzz ccc ccc ggg hhh ccc ccc xxx zzz xxx yyy ggg hhh xxx yyy xxx zzz hhh zzz ggg hhh hhh zzz xxx zzz
SELECT * FROM Table1 NATURAL JOIN Table2
Column1 Column2 Column1 Column3 xxx yyy xxx zzz
NATURAL JOIN を実行する場合、Caché は同じ名前を持つ列を統合しません。
SELECT * FROM Table1 INNER JOIN Table2
ON Table1.Column1=Table2.Column3
Column1 Column2 Column1 Column3 hhh zzz ggg hhh
SELECT * FROM Table1 INNER JOIN Table2
USING (Column1)
Column1 Column2 Column1 Column3 xxx yyy xxx zzz
SELECT * FROM Table1 LEFT OUTER JOIN Table2
ON Table1.Column1=Table2.Column3
Column1 Column2 Column1 Column3 aaa bbb null null ccc ccc null null xxx yyy null null hhh zzz ggg hhh
SELECT * FROM Table1 RIGHT OUTER JOIN Table2
ON Table1.Column1=Table2.Column3
Column1 Column2 Column1 Column3 hhh zzz ggg hhh null null xxx zzz