外結合の on句に片一方のテーブルだけの条件を書く

2013年12月27日コンピュータ

内結合(内部結合 / INNER JOIN )のとき、WHERE条件に書く代わりにときどき ON句に条件を書くことがある。
これはまぁ問題無いはずで意図通りに動作する。

しかしながら、外結合(外部結合 / OUTER JOIN )の場合はどうなるか。
いまいち自信がなかったので試してみた。

試したのは SQL Server 2014 CTP2 で。

まず、テスト用のテーブル作成

CREATE TABLE T1 (F1 int);
CREATE TABLE T2 (G1 int,G2 int);

で、値を突っ込む。

INSERT INTO T1 VALUES(1),(2),(3),(4);
INSERT INTO T2 VALUES(1,1),(1,2),(2,1),(3,2),(5,3);

とりあえず、中身。

SELECT * FROM T1;

F1
-----------
1
2
3
4

SELECT * FROM T2;

G1 G2
----------- -----------
1 1
1 2
2 1
3 2
5 3

まず、ふつーに INNER JOIN と OUTER JOIN

SELECT F1,G1,G2
FROM T1 INNER JOIN T2 ON T1.F1 = T2.G1;

F1 G1 G2
----------- ----------- -----------
1 1 1
1 1 2
2 2 1
3 3 2

SELECT F1,G1,G2
FROM T1 LEFT OUTER JOIN T2 ON T1.F1 = T2.G1;

F1 G1 G2
----------- ----------- -----------
1 1 1
1 1 2
2 2 1
3 3 2
4 NULL NULL

SELECT F1,G1,G2
FROM T1 RIGHT OUTER JOIN T2 ON T1.F1 = T2.G1;

F1 G1 G2
----------- ----------- -----------
1 1 1
1 1 2
2 2 1
3 3 2
NULL 5 3

SELECT F1,G1,G2
FROM T1 FULL OUTER JOIN T2 ON T1.F1 = T2.G1;

F1 G1 G2
----------- ----------- -----------
1 1 1
1 1 2
2 2 1
3 3 2
4 NULL NULL
NULL 5 3

さて、まず、LEFT OUTER JOIN で右だけの条件を追加する。

SELECT F1,G1,G2
FROM T1 LEFT OUTER JOIN T2 ON T1.F1 = T2.G1 AND T2.G1 = 1;

F1 G1 G2
----------- ----------- -----------
1 1 1
1 1 2
2 NULL NULL
3 NULL NULL
4 NULL NULL

こんどは左だけの条件を追加する。

SELECT F1,G1,G2
FROM T1 LEFT OUTER JOIN T2 ON T1.F1 = T2.G1 AND T1.F1 = 1;

F1 G1 G2
----------- ----------- -----------
1 1 1
1 1 2
2 NULL NULL
3 NULL NULL
4 NULL NULL

今度は右外結合で結びつけている列とは関係ない列の条件で。

SELECT F1,G1,G2
FROM T1 RIGHT OUTER JOIN T2 ON T1.F1 = T2.G1 AND T2.G2 = 2;

F1 G1 G2
----------- ----------- -----------
NULL 1 1
1 1 2
NULL 2 1
3 3 2
NULL 5 3

なぜこうなるのか?
「外部結合の使用」
http://technet.microsoft.com/ja-jp/library/ms187518.aspx
の説明では

「左外部結合の場合、参照される左側のテーブルからすべての行が取得されます」

とある。
その上で左外結合を説明すると

左のテーブルの各レコードに対し、
右のテーブルの各レコードと突き合わせて
ON句の条件が成立するものを結び付け、該当するものが無い場合には NULL にする

となる。

全外結合(完全外部結合) の場合で同様に考えると

SELECT F1,G1,G2
FROM T1 FULL OUTER JOIN T2 ON 1=0;

は

F1 G1 G2
----------- ----------- -----------
1 NULL NULL
2 NULL NULL
3 NULL NULL
4 NULL NULL
NULL 1 1
NULL 1 2
NULL 2 1
NULL 3 2
NULL 5 3

となり、

SELECT F1,G1,G2
FROM T1 FULL OUTER JOIN T2 ON 1=1;

は

F1 G1 G2
----------- ----------- -----------
1 1 1
1 1 2
1 2 1
1 3 2
1 5 3
2 1 1
2 1 2
2 2 1
2 3 2
2 5 3
3 1 1
3 1 2
3 2 1
3 3 2
3 5 3
4 1 1
4 1 2
4 2 1
4 3 2
4 5 3