【SQL】JOINの結合条件を理解する

やること

内部結合（INNER JOIN）と左外部結合（LEFT OUTER JOIN）を使ってJOINの結合条件の動きを学習したので備忘録として残します。
※RDBMSはPostgreSQLを使います。

データセット用意

以下DDLとINSERT文にてデータセットを作成します。

# DDL（AreaMapテーブル）
CREATE TABLE AreaMap (
	area VARCHAR(10),
	ctrCode VARCHAR(2),
	PRIMARY KEY (area,ctrCode)
);

# INSERT文
INSERT INTO AreaMap(area,ctrCode) VALUES('東京','JP');
INSERT INTO AreaMap(area,ctrCode) VALUES('大阪','JP');
INSERT INTO AreaMap(area,ctrCode) VALUES('仙台','JP');
INSERT INTO AreaMap(area,ctrCode) VALUES('ニューヨーク','US');
INSERT INTO AreaMap(area,ctrCode) VALUES('カリフォルニア','US');
INSERT INTO AreaMap(area,ctrCode) VALUES('上海','CN');
INSERT INTO AreaMap(area,ctrCode) VALUES('ロンドン','UK');
INSERT INTO AreaMap(area,ctrCode) VALUES('トロント','CA');

全件抽出すると以下のような結果セットが返ってきます。

# SELECT文
SELECT * FROM AreaMap;

【結果セット】

内部結合する

では、AreaMapテーブル同士をctrCodeを結合条件に内部結合してみます。

# SELECT文
SELECT a.area, b.area, a.ctrCode 
FROM AreaMap a
INNER JOIN AreaMap b
ON a.ctrCode = b.ctrCode;

【結果セット】

もともとのレコード数が8行に対して本結果セットは16行、、、
だいぶ多いですね、、、

結合条件の動き

ここで、結合条件の動きを確認します。

まず、INNER JOINの左側の結果が取得されます。

次に結合条件であるa.ctrcode = b.ctrcodeが動きます。
左側の結果に対して、ctrcodeの一致するレコードが直積されます。

よって以下のような結果セットになります。

左側結果セットの東京・JPに対して右側結果セットの東京・JP、大阪・JP、仙台・JPが結合条件に当てはまり3行の結果セットが生成される、左側結果セットの大阪・JPに対して右側結果セットの東京・JP、大阪・JP、仙台・JPが結合条件に当てはまり3行の結果セットが生成される、、、
ということが繰り返されて上記結果セットが出来上がるイメージですかね。

※内部的にどのようなアルゴリズムで結合しているかは分かりません。
※最終的な結果セットは並べ替えられていますが、今回は割愛します。

結合条件を追加する。

では、結合条件にa.area = '東京'を加えたいと思います。

# SELECT文
SELECT a.area, b.area, a.ctrCode 
FROM AreaMap a
INNER JOIN AreaMap b
ON a.ctrCode = b.ctrCode 
	AND a.area = '東京';

【結果セット】

だいぶ減りましたね。

結合条件の動き

条件を追加したことで以下のように結合されました。

まず、左側の結果が取得される。

次に結合条件a.ctrcode = b.ctrcode AND a.area = '東京'が動きます。
ctrcodeが一致し、かつ左側の結果のareaが東京のレコードのみが結合します。

内部結合では結合できたレコードのみを結果セットとして返すため、以下3レコードが返ってきます。

左外部結合（LEFT OUTER JOIN）の場合は？？

左外部結合は左側の結果をすべて結果セットに加え、右側は結合できたものを加え、結合できない場合はNULLをセットします。

まず、a.ctrcode = b.ctrcodeで単純結合した場合は全量結合できるため、内部結合のときと同じ結果セットになります。

# SELECR文
SELECT a.area, b.area, a.ctrCode 
FROM AreaMap a
LEFT OUTER JOIN AreaMap b
ON a.ctrCode = b.ctrCode;

【結果セット】

次に、結合条件にa.area = '東京'を加えた場合の動きを見てみます。
まず左側の結果はすべて結果セットに含まれます。
次に右側の結果のうち、結合されるのは以下の3レコードです。

それ以外の右側の結果にはNULLがセットされます
よって、最終的な結果セットは以下のようになります。

# SELECT文
SELECT a.area, b.area, a.ctrCode 
FROM AreaMap a
LEFT OUTER JOIN AreaMap b
ON a.ctrCode = b.ctrCode 
	AND a.area = '東京';

【結果セット】