LEFT JOIN／RIGHT JOIN／FULL JOIN／CROSS JOINについて

今日2回目の記事だ


仕事をしているとよく「INNER JOINって何だっけ？」とか
「FULL JOINってやるとどうなんだっけ？」ってよく聞かれる
たぶんデータベーススペシャリストを持ってるからだと思う。

でも、恥ずかしながら、俺もたまに混乱して分からなくなってしまう
(まぐれで取ったようなもんだから...)

で、データ／SQL／結果という観点でまとめてみることにした

なお、スクロールせずに比較できるように、情報をなるべく一画面に収めるように心掛けた。
このため、説明等はあまりしていない。説明を見たい方は別サイトを参照くださいm(_ _)m

また、下の方にSQL Server 2005での実データでの実験もしているので、参考にどうぞ。

＜データ＞
■テーブル左 (テーブル右と番号が共通している行は、100と300)
　　番号　名前
　　100　 A
　　102　 B
　　300　 C
■テーブル右(テーブル右と番号が共通している行は、100と300)
　　番号　値段
　　100　 100円
　　200　 200円
　　300　 300円

■INNER JOIN
　＜SQL＞
　SELECT 左.番号, 名前, 右.番号, 値段 FROM テーブル左 左 INNER JOIN テーブル右 右 ON 左.番号 = 右.番号
　＜結果＞
　　左番号　名前　右番号　値段
　　100　　　A　　100　　　100円
　　300　　　C　　300　　　300円

■LEFT (OUTER) JOIN
　＜SQL＞
　SELECT 左.番号, 名前, 右.番号, 値段 FROM テーブル左 左 LEFT (OUTER) JOIN テーブル右 右 ON 左.番号 = 右.番号
　＜結果＞
　　左番号　名前　右番号　値段
　　100　　 A　　 100　　　100円
　　102　　 B　　 NULL　　NULL
　　300　　 C　　 300　　　300円

■RIGHT (OUTER) JOIN
　＜SQL＞
　SELECT 左.番号, 名前, 右.番号, 値段 FROM テーブル左 左 RIGHT (OUTER) JOIN テーブル右 右 ON 左.番号 = 右.番号
　＜結果＞
　　左番号　名前　右番号　値段
　　100　　 A　　 100　　 100円
　　NULL　NULL 200　　 200円
　　300　　 C　　 300　　 300円

■FULL (OUTER) JOIN
　＜SQL＞
　SELECT 左.番号, 名前, 右.番号, 値段 FROM テーブル左 左 FULL (OUTER) JOIN テーブル右 右 ON 左.番号 = 右.番号
　＜結果＞
　　左番号　名前　右番号　値段
　　100　 A　　　 100　　 100円
　　102　 B　　　 NULL　　NULL
　　NULL NULL　 200　　 200円
　　300　 C　　　 300　　 300円

■CROSS JOIN
　＜SQL＞
　SELECT 左.番号, 名前, 右.番号, 値段 FROM テーブル左 左 LEFT OUTER JOIN テーブル右 右 ON 左.番号 = 右.番号
　＜結果＞
　　左番号　名前　右番号　値段
　　100　　 A　　 100　　 100円
　　102　　 B　　 100　　 100円
　　300　　 C　　 100　　 100円
　　100　　 A　　 200　　 200円
　　102　　 B　　 200　　 200円
　　300　　 C　　 200　　 200円
　　100　　 A　　 300　　 300円
　　102　　 B　　 300　　 300円
　　300　　 C　　 300　　 300円


---------------------------------------
ここから↓はSQL Server 2005での実行例
---------------------------------------
■テーブルの作成とデータのインサート
　1> create table LeftTbl(No char(3), name varchar(10));
　2> go
　1> create table RightTbl(No char(3), price decimal(4));
　2> go
　1> insert into LeftTbl values ('102', 'B')
　2> insert into LeftTbl values ('100', 'A')
　3> insert into LeftTbl values ('300', 'C')
　4> go
　(1 行処理されました)
　1> insert into RightTbl values ('100', 100)
　2> insert into RightTbl values ('200', 200)
　3> insert into RightTbl values ('300', 300)
　4> go
　(1 行処理されました)


■左テーブルの表示
　1> select * from LeftTbl order by No
　2> go
　No　name
　--- ----------
　100 A
　102 B
　300 C

　(3 行処理されました)

■右テーブルの表示
　1> select * from RightTbl order by No
　2> go
　No　price
　--- ------
　100　　100
　200　　200
　300　　300

　(3 行処理されました)

　⇒ priceに「円」が付いてないのは、愛嬌ってことで(> <)

■INNER JOINの実行
　1> SELECT L.No 左番号, name 名前, R.No 右番号, price 値段 FROM LeftTbl L INNER JOIN RightTbl R ON L.No = R.No;
　2> go
　左番号 名前　右番号 値段
　---　　----　---　　------
　100　　A　　 100　　100
　300　　C　　 300　　300

　(2 行処理されました)

■LEFT (OUTER) JOINの実行
1> SELECT L.No 左番号, name 名前, R.No 右番号, price 値段 FROM LeftTbl L LEFT OUTER JOIN RightTbl R ON L.No = R.No;
2>
3> go
　左番号 名前　右番号 値段
　---　　----　---　　------
　100　　A　　 100　　100
　102　　B　　 NUL　　NULL
　300　　C　　 300　　300

　(3 行処理されました)

■RIGHT (OUTTER) JOINの実行
1> SELECT L.No 左番号, name 名前, R.No 右番号, price 値段 FROM LeftTbl L RIGHT OUTER JOIN RightTbl R ON L.No = R.No;
2>
3> go
　左番号 名前　右番号 値段
　---　　----　---　　------
　100　　A　　 100　　100
　NUL　　NULL　200　　200
　300　　C　　 300　　300

　(3 行処理されました)

■FULL (OUTTER) JOINの実行
　1> SELECT L.No 左番号, name 名前, R.No 右番号, price 値段 FROM LeftTbl L FULL OUTER JOIN RightTbl R ON L.No = R.No;
　2>
　3> go
　左番号 名前　右番号 値段
　---　　----　---　　------
　100　　A　　 100　　100
　102　　B　　 NUL　　NULL
　300　　C　　 300　　300
　NUL　　NULL　200　　200

　(4 行処理されました)

■CROSS JOINの実行
　1> SELECT L.No 左番号, name 名前, R.No 右番号, price 値段 FROM LeftTbl L CROSS JOIN RightTbl R;
　2> go
　左番号 名前　右番号 値段
　---　　----　---　　------
　100　　A　　 100　　100
　102　　B　　 100　　100
　300　　C　　 100　　100
　100　　A　　 200　　200
　102　　B　　 200　　200
　300　　C　　 200　　200
　100　　A　　 300　　300
　102　　B　　 300　　300
　300　　C　　 300　　300

　(9 行処理されました)