IS DISTINCT FROMとIS NOT DISTINCT FROM

入力のどちらかがNULLの場合、通常の比較演算子は（”不明”を意味する）NULLを生成します。比較を行う他の方法として、以下のようにIS [ NOT ] DISTINCT FROM構文があります。

expression IS DISTINCT FROM expression

expression IS NOT DISTINCT FROM expression

非NULLの入力では、IS DISTINCT FROMは<>演算子と同じです。 しかし、入力がどちらもNULLの場合、これは偽を返し、片方の入力のみがNULLの場合は真を返します。 同様に、IS NOT DISTINCT FROMは非NULL入力では=と同じですが、両方の入力がNULLであれば真を、片方のみがNULLの場合は偽を返します。 このように、これらの構文はNULLを”不明な値”ではなく、通常の値かのように動作します。

Documentation | EnterpriseDB

IS DISTINCT FROM

SELECT 1 IS DISTINCT FROM 1;

?column? 

----------

f

(1 row)

SELECT NULL IS DISTINCT FROM NULL;

?column? 

----------

f

(1 row)

SELECT NULL IS DISTINCT FROM 1;

?column? 

----------

t

(1 row)

IS NOT DISTINCT FROM

SELECT 1 IS NOT DISTINCT FROM 1;

?column? 

----------

t

(1 row)

SELECT NULL IS NOT DISTINCT FROM NULL;

?column? 

----------

t

(1 row)

SELECT 1 IS NOT DISTINCT FROM NULL;

?column? 

----------

f

(1 row)

実験用のレコードを用意

SELECT c1,c2,c3 FROM test1s;

c1  | c2  | c3  

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

AAA | BBB | CCC

DDD |     | FFF

GGG | HHH | 

(3 rows)

SELECT c1,c2,c3 FROM test2s;

c1  | c2  | c3  

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

AAA | BBB | CCC

DDD |     | FFF

GGG | HHH | 

(3 rows)

=で結合した場合

SELECT t1.c1, t1.c2, t1.c3, t2.c1, t2.c2, t2.c3 

FROM test1s AS t1 

INNER JOIN test2s AS t2 ON (t1.c3 = t2.c3);

c1  | c2  | c3  | c1  | c2  | c3  

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

AAA | BBB | CCC | AAA | BBB | CCC

DDD |     | FFF | DDD |     | FFF

(2 rows)

SELECT t1.c1, t1.c2, t1.c3, t2.c1, t2.c2, t2.c3 

FROM test1s AS t1 

LEFT OUTER JOIN test2s AS t2 ON (t1.c3 = t2.c3);

c1  | c2  | c3  | c1  | c2  | c3  

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

AAA | BBB | CCC | AAA | BBB | CCC

DDD |     | FFF | DDD |     | FFF

GGG | HHH |     |     |     | 

(3 rows)

IS NOT DISTINCT FROMで結合してみる

SELECT t1.c1, t1.c2, t1.c3, t2.c1, t2.c2, t2.c3 FROM test1s AS t1 

LEFT OUTER JOIN test2s AS t2 ON (t1.c3 IS NOT DISTINCT FROM t2.c3);

c1  | c2  | c3  | c1  | c2  | c3  

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

AAA | BBB | CCC | AAA | BBB | CCC

DDD |     | FFF | DDD |     | FFF

GGG | HHH |     | GGG | HHH | 

(3 rows)

SELECT t1.c1, t1.c2, t1.c3, t2.c1, t2.c2, t2.c3 

FROM test1s AS t1 INNER JOIN test2s AS t2 ON (t1.c3 IS NOT DISTINCT FROM t2.c3);

c1  | c2  | c3  | c1  | c2  | c3  

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

AAA | BBB | CCC | AAA | BBB | CCC

DDD |     | FFF | DDD |     | FFF

GGG | HHH |     | GGG | HHH | 

(3 rows)

costを比較してみる

• =で結合する
• IS NOT DISTINCT FROMで結合する

どっちもcostは同じみたい。時間を作って、100万件レベルでのexplain analyze比較もしてみたい。

explain analyze 

SELECT t1.c1, t1.c2, t1.c3, t2.c1, t2.c2, t2.c3 

FROM test1s AS t1 INNER JOIN test2s AS t2 

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

QUERY PLAN                                                     

--------------------------------------------------------------------------------------------------------------------

Nested Loop  (cost=10.55..77.30 rows=50 width=3096) (actual time=0.018..0.027 rows=3 loops=1)

Join Filter: (((t1.c3)::text = (t2.c3)::text) OR ((t1.c3 IS NULL) AND (t2.c3 IS NULL)))

->  Seq Scan on test1s t1  (cost=0.00..10.50 rows=50 width=1548) (actual time=0.003..0.003 rows=3 loops=1)

->  Materialize  (cost=10.55..11.05 rows=50 width=1548) (actual time=0.004..0.005 rows=3 loops=3)

->  Seq Scan on test2s t2  (cost=0.00..10.50 rows=50 width=1548) (actual time=0.003..0.005 rows=3 loops=1)

Total runtime: 0.106 ms

(6 rows)

explain analyze 

SELECT t1.c1, t1.c2, t1.c3, t2.c1, t2.c2, t2.c3 FROM test1s AS t1 

INNER JOIN test2s AS t2 

ON (t1.c3 IS NOT DISTINCT FROM t2.c3);

QUERY PLAN                                                     

--------------------------------------------------------------------------------------------------------------------

Nested Loop  (cost=10.55..77.30 rows=1250 width=3096) (actual time=0.021..0.031 rows=3 loops=1)

Join Filter: (NOT ((t1.c3)::text IS DISTINCT FROM (t2.c3)::text))

->  Seq Scan on test1s t1  (cost=0.00..10.50 rows=50 width=1548) (actual time=0.005..0.006 rows=3 loops=1)

->  Materialize  (cost=10.55..11.05 rows=50 width=1548) (actual time=0.004..0.006 rows=3 loops=3)

->  Seq Scan on test2s t2  (cost=0.00..10.50 rows=50 width=1548) (actual time=0.003..0.004 rows=3 loops=1)

Total runtime: 0.088 ms

(6 rows)