INNER JOINとLEFT OUTER JOINで更に実験してみる

公開日: : 最終更新日:2013/11/11 SQL

記事内に広告を含む場合があります。記事内で紹介する商品を購入することで、当サイトに売り上げの一部が還元されることがあります。

テーブル定義

test3sにはunique indexを作成してある。


postgres_test_development=# \d test1s
Table "public.test1s"
Column | Type | Modifiers
--------+------------------------+-----------------------------------------------------
id | integer | not null default nextval('test1s_id_seq'::regclass)
c1 | character varying(255) |
c2 | character varying(255) |
Indexes:
"test1s_pkey" PRIMARY KEY, btree (id)

postgres_test_development=# \d test3s
Table "public.test3s"
Column | Type | Modifiers
--------+------------------------+-----------------------------------------------------
id | integer | not null default nextval('test3s_id_seq'::regclass)
c1 | character varying(255) |
c2 | character varying(255) |
Indexes:
"test3s_pkey" PRIMARY KEY, btree (id)
"index_test3s_on_c1_and_c2" UNIQUE, btree (c1, c2)

レコード

test3sにはunique indexを作成してあるが、NULLの重複は許される。


SELECT * FROM test1s;

id | c1 | c2
----+-----+-----
1 | AAA | BBB
2 | CCC | DDD
3 | EEE |
(3 rows)

SELECT * FROM test3s;

id | c1 | c2
----+-----+-----
1 | BBB | 111
3 | DDD | 222
4 | | 333
5 | | 333
6 | | 444
(5 rows)

こんな実行結果を得られるSQLを考えてみる


c1 | c2 | c1 | c2
-----+-----+-----+-----
AAA | BBB | BBB | 111
CCC | DDD | DDD | 222
EEE | | | 333
EEE | | | 444

DISTINCT

DISTINCTはNULLを同じ値だと判断してくれるみたい。


SELECT DISTINCT c1, c2 FROM test3s;

c1 | c2
-----+-----
BBB | 111
DDD | 222
| 333
| 444
(4 rows)

DISTINCTしてINNER JOIN


SELECT t1.c1, t1.c2, t3.c1, t3.c2
FROM test1s AS t1
INNER JOIN (SELECT DISTINCT c1, c2 FROM test3s) AS t3
ON (t1.c2 = t3.c1);

c1 | c2 | c1 | c2
-----+-----+-----+-----
AAA | BBB | BBB | 111
CCC | DDD | DDD | 222
(2 rows)

DISTINCTしてLEFT OUTER JOIN


SELECT t1.c1, t1.c2, t3.c1, t3.c2
FROM test1s AS t1
LEFT OUTER JOIN (SELECT DISTINCT c1, c2 FROM test3s) AS t3
ON (t1.c2 = t3.c1);

c1 | c2 | c1 | c2
-----+-----+-----+-----
AAA | BBB | BBB | 111
CCC | DDD | DDD | 222
EEE | | |
(3 rows)

DISTINCTしてINNER JOIN(ONでNULL同士も結合)

やっと目的の結果が得られた。


SELECT t1.c1, t1.c2, t3.c1, t3.c2
FROM test1s AS t1
INNER JOIN (SELECT DISTINCT c1, c2 FROM test3s) AS t3
ON (t1.c2 = t3.c1 OR (t1.c2 IS NULL AND t3.c1 IS NULL));

c1 | c2 | c1 | c2
-----+-----+-----+-----
AAA | BBB | BBB | 111
CCC | DDD | DDD | 222
EEE | | | 333
EEE | | | 444
(4 rows)

DISTINCTしてLEFT OUTER JOIN(ONでNULL同士も結合)

ONでNULL同士も結合する場合、LEFT OUTER JOINでもOKみたい。


SELECT t1.c1, t1.c2, t3.c1, t3.c2
FROM test1s AS t1
LEFT OUTER JOIN (SELECT DISTINCT c1, c2 FROM test3s) AS t3
ON (t1.c2 = t3.c1 OR (t1.c2 IS NULL AND t3.c1 IS NULL));

c1 | c2 | c1 | c2
-----+-----+-----+-----
AAA | BBB | BBB | 111
CCC | DDD | DDD | 222
EEE | | | 333
EEE | | | 444
(4 rows)

explain analyzeして比較してみる

ONでNULL同士も結合した場合、INNER JOINとLEFT OUTER JOIN、どっちを使っても同じみたい。でも、もっと速いSQLがあるかもしれないなあ。


SELECT t1.c1, t1.c2, t3.c1, t3.c2
FROM test1s AS t1
INNER JOIN (SELECT DISTINCT c1, c2 FROM test3s) AS t3
ON (t1.c2 = t3.c1 OR (t1.c2 IS NULL AND t3.c1 IS NULL));

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=14.14..135.09 rows=70 width=2064) (actual time=2.100..2.213 rows=4 loops=1)
Join Filter: ((("outer".c2)::text = ("inner".c1)::text) OR (("outer".c2 IS NULL) AND ("inner".c1 IS NULL)))
-> Seq Scan on test1s t1 (cost=0.00..10.70 rows=70 width=1032) (actual time=0.279..0.319 rows=3 loops=1)
-> Materialize (cost=14.14..14.84 rows=70 width=1032) (actual time=0.573..0.586 rows=4 loops=3)
-> Unique (cost=12.85..13.37 rows=70 width=1032) (actual time=1.679..1.700 rows=4 loops=1)
-> Sort (cost=12.85..13.02 rows=70 width=1032) (actual time=1.675..1.680 rows=5 loops=1)
Sort Key: test3s.c1, test3s.c2
-> Seq Scan on test3s (cost=0.00..10.70 rows=70 width=1032) (actual time=0.069..0.080 rows=5 loops=1)
Total runtime: 3.270 ms
(9 rows)

SELECT t1.c1, t1.c2, t3.c1, t3.c2
FROM test1s AS t1
LEFT OUTER JOIN (SELECT DISTINCT c1, c2 FROM test3s) AS t3
ON (t1.c2 = t3.c1 OR (t1.c2 IS NULL AND t3.c1 IS NULL));

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=14.14..135.09 rows=70 width=2064) (actual time=0.417..0.528 rows=4 loops=1)
Join Filter: ((("outer".c2)::text = ("inner".c1)::text) OR (("outer".c2 IS NULL) AND ("inner".c1 IS NULL)))
-> Seq Scan on test1s t1 (cost=0.00..10.70 rows=70 width=1032) (actual time=0.006..0.043 rows=3 loops=1)
-> Materialize (cost=14.14..14.84 rows=70 width=1032) (actual time=0.102..0.115 rows=4 loops=3)
-> Unique (cost=12.85..13.37 rows=70 width=1032) (actual time=0.299..0.319 rows=4 loops=1)
-> Sort (cost=12.85..13.02 rows=70 width=1032) (actual time=0.295..0.303 rows=5 loops=1)
Sort Key: test3s.c1, test3s.c2
-> Seq Scan on test3s (cost=0.00..10.70 rows=70 width=1032) (actual time=0.068..0.111 rows=5 loops=1)
Total runtime: 0.794 ms
(9 rows)

TODO

テーブルのレコードを100万行くらいにして実験してみる。

追記(2008/12/30)

SQLを基礎からみっちり学ぶのであればこの本が良い。手書きでSQLを書きまくることによって慣れていくドリル形式になっており、SQLを書く感覚が身につくと思う。

関連記事

サンフランシスコのピア39にあるチャウダーズでクラムチャウダーを食す!

lolipop アップルの開発者向けイベント「WWDC2014」

ミスドのカルピスドーナツとカルピスポンデリングを食べてみた!

ミスドで期間限定のカルピスコラボ商品「カルピスドーナツ」と「カルピ

十三カレー計画で牛すじカレーネギのせを食す!(大阪・十三)

「iPhoneアプリ開発キャンプ@大阪」のランチで、十三カレー計画

大阪・難波の加寿屋 法善寺でかすうどんを食す。ランチタイムはおにぎり2個まで無料!

大阪・難波の加寿屋 法善寺 (かすうどん KASUYA)で、かす

ライブドアブログで運営していた「あきお商店」を「卵は世界である」に改名しました

少し前からライブドアブログで「あきお商店」というブログをやって

→もっと見る

PAGE TOP ↑