いろいろなJOIN 2013年4月20日 (土) 23:03時点におけるDondariadmin (トーク | 投稿記録)による版 (差分) ← 古い版 | 最新版 (差分) | 新しい版 → (差分)

JOIN句

• SELECTに欠かせないJOINですが、私の場合、普段よく使うのは、INNER JOINとLEFT JOINぐらいです。が、他にもあるので、勉強してみました。
• DBMSは手元にあったOracle 10g expressです。

1. table_a NATURAL JOIN table_b
   1. table_aとtable_bの同名列を結合条件としたINNER JOINと等価。
   2. table_a INNER JOIN table_b USING (列名)
2. table_a CROSS JOIN table_b
   1. FROM table_a, table_b と記述した場合と等価。いわゆる直積、デカルト積。得られる結果レコード数は2テーブルのレコード数の積。
3. table_a FULL JOIN table_b ＜ ON 結合条件式 ｜ USING (列名) ＞
   1. table_aとtable_bの両テーブルのレコードすべてが含まれる。一致する場合は結合され、一致しない部分はNULLとなる。
   2. LEFT JOINとRIGHT JOINをUNIONした結果と同等。

サンプルテーブル準備

CREATE TABLE shouhin
( shouhin_code INT
, shouhin_name VARCHAR(100)
, shouhin_bunrui_code INT
);
CREATE TABLE shouhin_bunrui
( shouhin_bunrui_code INT
, shouhin_bunrui_name VARCHAR(100)
);
INSERT INTO shouhin VALUES (1,'ボールペン黒',10);
INSERT INTO shouhin VALUES (2,'ボールペン赤',10);
INSERT INTO shouhin VALUES (3,'ノート無地',20);
INSERT INTO shouhin_bunrui VALUES (10,'ペン');
INSERT INTO shouhin_bunrui VALUES (30,'用紙');

確認

NATURAL JOIN

• 共通の列で結合され、結果の先頭列に昇格してますね。
• SHOUHIN_BUNRUI_CODEがひとつにまとまってしまうのは、INNER JOIN ～ USINGと同じです。
• 下記SQLもまったく同じ結果が得られます。

SELECT * FROM shouhin INNER JOIN shouhin_bunrui USING (shouhin_bunrui_code);

CROSS JOIN編

• 直積（デカルト積）ですから、そのまんまですね。shouhinテーブルの3レコード×shouhin_bunruiテーブルの2レコード＝6レコード。
• 片方のテーブルがゼロレコードの場合は当然結果もゼロレコードです。
• 一般的にはこのままでは用途があまり無いと思われますので、WHERE句に結合条件を記述することになると思います。
• 下記SQLもまったく同じ結果が得られます。

SELECT * FROM shouhin, shouhin_bunrui;

FULL JOIN編

• 1,2行目はshouhin_bunrui_codeで結合されるので、NATURAL JOINの結果と同じです。
• 3行目は左側のテーブルのみ存在している結果です。LEFT JOINの結果の中で、条件不一致だったレコードと同じです。
• 4行目は逆に右側のテーブルのみ存在している結果です。RIGHT JOINの結果の中で、条件不一致だったレコードと同じです。
• つまり、LEFT JOINの結果とRIGHT JOINの結果をマージしたことになります。よって、下記SQLと同じ結果になります。

SELECT * FROM shouhin LEFT JOIN shouhin_bunrui USING (shouhin_bunrui_code)
UNION
SELECT * FROM shouhin RIGHT JOIN shouhin_bunrui USING (shouhin_bunrui_code)

Tweet