結合条件のカラムにNULLが入っているとJOINできない件について

公開日： 2008/04/16 : 最終更新日：2013/11/11 SQL

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

結合条件のカラムにNULLが入っているとJOINできない。例えば以下のようなテーブルがあったとする。



class CreateTest1s < ActiveRecord::Migration

def self.up

create_table :test1s do |t|

t.column :c1, :string

t.column :c2, :string

t.column :c3, :string

t.timestamps

end

end



def self.down

drop_table :test1s

end

end



class CreateTest2s < ActiveRecord::Migration

def self.up

create_table :test2s do |t|

t.column :c1, :string

t.column :c2, :string

t.column :c3, :string

t.timestamps

end

end



def self.down

drop_table :test2s

end

end

データをINSERTする。



>> Test1.create(:c1=>'AAA', :c2=>'BBB', :c3=>'CCC')

>> Test1.create(:c1=>'DDD', :c2=>nil, :c3=>'FFF')

>> Test1.create(:c1=>'GGG', :c2=>'HHH', :c3=>nil)

>> Test2.create(:c1=>'AAA', :c2=>'BBB', :c3=>'CCC')

>> Test2.create(:c1=>'DDD', :c2=>nil, :c3=>'FFF')

>> Test2.create(:c1=>'GGG', :c2=>'HHH', :c3=>nil)

内容をチェック。



sql-test=# SELECT id, c1, c2, c3 FROM test1s;

id | c1  |   c2   |   c3   

----+-----+--------+--------

1 | AAA | BBB    | CCC

3 | DDD | (null) | FFF

4 | GGG | HHH    | (null)

(3 rows)

sql-test=# SELECT id, c1, c2, c3 FROM test2s;

id | c1  |   c2   |   c3   

----+-----+--------+--------

1 | AAA | BBB    | CCC

2 | DDD | (null) | FFF

3 | GGG | HHH    | (null)

(3 rows)

JOINしてみる。



sql-test=# SELECT t1.c1, t2.c1 FROM test1s AS t1 JOIN test2s AS t2 ON (t1.c1 = t2.c1);

c1  | c1  

-----+-----

AAA | AAA

DDD | DDD

GGG | GGG

(3 rows)

sql-test=# SELECT t1.c1, t2.c1 FROM test1s AS t1 JOIN test2s AS t2 ON (t1.c1 = t2.c1 AND t1.c2 = t2.c2);

c1  | c1  

-----+-----

AAA | AAA

GGG | GGG

(2 rows)

sql-test=# SELECT t1.c1, t2.c1 FROM test1s AS t1 JOIN test2s AS t2 ON (t1.c1 = t2.c1 AND t1.c2 = t2.c2 AND t1.c3 = t2.c3);

c1  | c1  

-----+-----

AAA | AAA

(1 row)

やっぱり結合条件にNULLが入っていると、結合結果からは除外されてしまう模様。

ここで「CASEを使って、NULLを他の値に置換してJOINしてみては?」というアドバイスが。



sql-test=# SELECT c1, CASE WHEN c2 IS NULL THEN 'QQQ' ELSE c2 END AS c2, CASE WHEN c3 IS NULL THEN 'QQQ' ELSE c3 END AS c3 FROM test1s;

c1  | c2  | c3  

-----+-----+-----

AAA | BBB | CCC

DDD | QQQ | FFF

GGG | HHH | QQQ

(3 rows)

よし、これでJOINするSQLを書いてみよう。



SELECT

*

FROM

(

SELECT

c1,

CASE WHEN c2 IS NULL THEN 'QQQ' ELSE c2 END AS c2,

CASE WHEN c3 IS NULL THEN 'QQQ' ELSE c3 END AS c3

FROM

test1s

) AS t1

JOIN

(

SELECT

c1,

CASE WHEN c2 IS NULL THEN 'QQQ' ELSE c2 END AS c2,

CASE WHEN c3 IS NULL THEN 'QQQ' ELSE c3 END AS c3

FROM

test2s

) AS t2

ON

(

t1.c1 = t2.c1

AND t1.c2 = t2.c2

AND t1.c3 = t2.c3

)

実行結果は以下の通り。



c1  | c2  | c3  | c1  | c2  | c3  

-----+-----+-----+-----+-----+-----

AAA | BBB | CCC | AAA | BBB | CCC

DDD | QQQ | FFF | DDD | QQQ | FFF

GGG | HHH | QQQ | GGG | HHH | QQQ

うーん、面倒。もっと良い方法ないかなあ…

id:Cress氏からコメントをもらった



Cress 2008/04/16 20:17

うぎゃあ、こうなったか・・・orz

後々考えてみたのですが、こういうのはダメでしょうか？一応検証済み。 = で結ぶ条件が増えてくるとややこしくなりますが・・・



SELECT * FROM test1s AS t1 JOIN test2s AS t2 ON (t1.c1 = t2.c1 OR (t1.c1 IS NULL AND t2.c1 IS NULL));

なるほど。早速試してみよう。



SELECT

t1.c1

,t1.c2

,t1.c3

,t2.c1

,t2.c2

,t2.c3

FROM

test1s AS t1

JOIN

test2s AS t2

ON (

(t1.c1 = t2.c1 OR (t1.c1 IS NULL AND t2.c1 IS NULL))

AND (t1.c2 = t2.c2 OR (t1.c2 IS NULL AND t2.c2 IS NULL))

AND (t1.c3 = t2.c3 OR (t1.c3 IS NULL AND t2.c3 IS NULL))

)



c1  | c2  | c3  | c1  | c2  | c3  

-----+-----+-----+-----+-----+-----

AAA | BBB | CCC | AAA | BBB | CCC

DDD |     | FFF | DDD |     | FFF

GGG | HHH |     | GGG | HHH | 

(3 rows)

おお、期待通りの結果が。Cress++