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

テーブル定義

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を書く感覚が身につくと思う。

改訂新版 反復学習ソフト付き SQL書き方ドリル (WEB+DB PRESS plusシリーズ)

• 作者: 羽生章洋,和田省二
• 出版社/メーカー: 技術評論社
• 発売日: 2007/05/18
• メディア: 大型本
• 購入: 16人 クリック: 211回
• この商品を含むブログ (51件) を見る