CROSS JOIN / 自然結合 / 指定結合 / UNION JOIN


SQL92 はある種の共通する結合操作を簡単に実行できるように、特別な構文を用意しています。

SQL のビルトイン結合演算子には、「CROSS JOIN」、「NATURAL JOIN」、「指定結合」及び「UNION JOIN」があります。

これらは次のように定義されています。

CROSS JOIN

結合述語のない結合に相当します。すなわち、ストレートなデカルト積です。

NATURAL JOIN

自然結合は一般に、外部キー値から親キー値への照合に基づいた結合を指します。
ただし、SQL92 規格ではこの用語が少し違った意味で使用されています。
SQL92 規格では、同じ名前を持つ 2つの列が親キーと外部キーであるかのように照合されます。
つまり、そのデータベース設計が列名の一致は実際に親キーと外部キーをあらわすと取り決めている場合に、NATURAL JOIN 演算子を使用すべきだと、いう考え方です。

指定結合

クエリに直接指定された条件に基づいた結合です。
クエリの WHERE 句の代わりに、FROM 句で結合を行うための機能を提供します。

UNION JOIN

明示的または暗黙的に指定された述語を指定せず、照合やデカルト積の抽出は行われません。
テーブル A のすべての行を取り込み、テーブル B の列に NULL を設定し、さらに テーブル B の行をすべて取り込み、テーブル A の列に NULL を設定した出力テーブルを生成します。

CROSS JOIN

CROSS JOINとは、結合テーブルの値の比較に基づいた選択を行わないデカルト積です。
つまり、すべての行の組み合わせが使用されます。

結合を実行し、結合述語を一切使用しない場合と効果は同じです。
この結合を実行した時、出力結果が膨大な量になります。

A表

01 AA
02 BB
03 CC

B表

11 TTT
22 UUU
/* テーブルの結合 */

 SELECT A.甲,A.乙,B.あ,B.い
        FROM A表 A CROSS JOIN B表 B ;
/* 実行結果 */

甲 乙 あ い
─ ─ ─ ──
01 AA 11 TTT
01 AA 22 UUU
02 BB 11 TTT
02 BB 22 UUU
03 CC 11 TTT
03 CC 22 UUU

自然結合

NATURAL INNER 結合

NATURAL JOIN の前後で指定されたテーブルの同じ名前のカラムの値を比較し、値が一致する行を出力します。

同じ名前のカラムが複数ある場合など、明示的にどのカラムの値を指定する場合は、ON句を使用します。

/* NATURAL INNER 結合 */

 SELECT 受注表.受注番号,顧客表.顧客名
        FROM 受注表 NATURAL JOIN 顧客表 ;
/* 実行結果 */
受注番号 顧客名
──── ──────
10001    KUROKIYA
10002    ONSIDE
10003    KUSHINANA
10004    KUSHINANA
10005    ONSIDE
10006    KUROKIYA
10007    FIRST HOUSE
10008    KUSHINANA
10009    FIRST HOUSE
10010    KUROKIYA
10011    FIRST HOUSE
10012    KUSHINANA

NATURAL LEFT OUTER 結合

テーブルAのすべての行と、テーブル B で一致が検出された行が含まれます。
テーブルBで一致が検出されなかった行には NULL が設定されます。

<テーブルA> [NATURAL] LEFT [OUTER] JOIN <テーブル B> ;
/* [NATURAL] LEFT [OUTER] 結合 */

 SELECT 受注表.受注番号,顧客表.顧客名
        FROM 受注表 NATURAL LEFT OUTER JOIN 顧客表
        WHERE 受注表.納品日 ='20010403' ;
/* 実行結果 */
受注番号 顧客名
──── ──────
10001    NULL
10002    NULL
10003    NULL
10004    NULL
10005    NULL
10006    NULL
10007    NULL
10008    KUSHINANA
10009    FIRST HOUSE
10010    KUROKIYA
10011    NULL
10012    NULL

NATURAL RIGHT OUTER 結合

LEFT OUTER 結合の反対です。
テーブルBのすべての行と、テーブルAで一致が検出された行が含まれ、テーブルAで一致が検出されなかった行には NULL が設定されます。

<テーブルA> [NATURAL] RIGHT [OUTER] JOIN <テーブル B> ;

NATURAL FULL OUTER 結合

LEFT OUTER と RIGHT OUTER の組み合わせです。
両方のテーブルからすべての行を追加し、一致する行を含み、一致しないものに NULL を設定します。

<テーブルA> [NATURAL] FULL [OUTER] JOIN <テーブルB> ;

指定結合

指定結合とは、結合を行う方法を指定する述語を作成する ON 形式か、等価結合を行う列集合を列挙する USING 形式という方法で、テーブルを結合する方法を直接指定する結合のことです。

指定結合

SELECT 受注表.受注番号,顧客表.顧客名
        FROM 受注表,顧客表
        ON 受注表.顧客コード = 顧客表.顧客コード ;
/* 実行結果 */
受注番号 顧客名
──── ──────
10001    KUROKIYA
10002    ONSIDE
10003    KUSHINANA
10004    KUSHINANA
10005    ONSIDE
10006    KUROKIYA
10007    FIRST HOUSE
10008    KUSHINANA
10009    FIRST HOUSE
10010    KUROKIYA
10011    FIRST HOUSE
10012    KUSHINANA

 

/* 指定結合 */

 SELECT 受注表.受注番号,顧客表.顧客名
        FROM 受注表,顧客表
        ON  受注表.顧客コード = 顧客表.顧客コード
        WHERE 顧客表.顧客名 = 'ONSIDE';
/* 実行結果 */
受注番号 顧客名
──── ──────
10002    ONSIDE
10005    ONSIDE

USING 形式は、引数としてカンマで区切られた列名リストを取り、同じ名前を持つ列のすべての組みに等価結合を実行し、AND を使って結果を組み合わせる。
つまり、同じ名前を持つ列をすべて利用して結合するのではなく、選択したものだけを使用してテーブルを結合する

 

/* 指定結合 */

 SELECT 受注表.受注番号,商品表.商品名
        FROM 受注表,商品表
        USING(商品コード)
        WHERE 商品表.単価 >= 500;
/* 実行結果 */
受注番号 商品名
──── ───
10002   BEER
10003   BEER
10005   WINE
10008   BEER
10010   BEER
10012   BEER

このSQL文は本当は USING 句を用いて結合の形式を指定する必要はありません。
それは、受注表と商品表の列名が同じなのは、商品コード一つだけだからです。

もし、二つある場合には明示的に指定しないと、期待したとおりのデータが参照できない場合があります。

また同じ名前の列が二つ以上ある場合、USING(列名,列名,….) として複数の列を指定して結合させることもできます。

指定結合には、自然結合で説明した INNER JOIN, LEFT [OUTER] JOIN, RIGHT [OUTER] JOIN, FULL [OUTER] JOIN のカテゴリがあります。
効果は自然結合の節で説明したのと同じです。
指定結合の場合、[NATURAL] は記述しないところに注意が必要です。

UNION JOIN

UNION JOIN はデカルト積をベースとせず、述語を使用しない結合です。

WHERE 句で述語を指定することはできますが、UNION JOIN は 2つのテーブルのデータを関連付けようとはしません。

テーブルAとテーブルBの UNION JOIN はテーブルAの列値をすべて出力し、同じ行のテーブルBの列を NULL で埋めます。
次に、テーブルBの列値をすべて出力し、同じ行のテーブルAの列を NULL で埋めます。

/* UNION 結合 */

 SELECT 受注表.受注番号,商品表.商品名
        FROM 受注表 UNION JOIN 顧客表 ;
/* 実行結果 */
受注番号 商品名
──── ──────
10001    NULL
10002    NULL
10003    NULL
10004    NULL
10005    NULL
10006    NULL
10007    NULL
10008    NULL
10009    NULL
10010    NULL
10011    NULL
10012    NULL
10013    NULL
NULL     BEER
NULL     JUICE
NULL     TEA
NULL     WINE

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です


− 5 = 4

次のHTML タグと属性が使えます: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>