is [not] distinct from 的 null 比较

--在postgresql中经常会比较两个值相同或者不同，但如果比较表达中有一个为null则会比较头痛，如下：


postgres=# select null <> null ,null = null ,null is null,null is not null;
 ?column? | ?column? | ?column? | ?column? 
----------+----------+----------+----------
          |          | t        | f
 
--创建测试数据
postgres=# create table t (id int,name varchar(20));
CREATE TABLE
 
postgres=# insert into t values(1,'rudy');
INSERT 0 1
postgres=# insert into t values(2);       
INSERT 0 1
postgres=# select * from t;
 id | name 
----+------
  1 | rudy
  2 | 
(2 rows)


--expression如果为null与任何值比较，都为null
postgres=# select * from t where null <> name;
 id | name 
----+------
(0 rows)


--is distinct from 对与非null其意为<>
postgres=# select * from t where 'rudy' is distinct from name; 
 id | name 
----+------
  2 | 
--is distinct from 对于expression为null,其会让与null的对比为值，非null对比其本身为假，is distinct from本身比较其是否不同
postgres=# select * from t where null is distinct from name;
 id | name 
----+------
  1 | rudy
(1 row)
--is not distinct from 对与非null其意为=
postgres=# select * from t where 'rudy' is not distinct from name;
 id | name 
----+------
  1 | rudy
--is distinct from 对于expression为null,其会让与null的对比为值，非null对比其本身为假，is distinct from本身比较其是相同  
postgres=# select * from t where null is not distinct from name;
 id | name 
----+------
  2 | 
  
--注意  is [not] distinct from 效率上不如<>,=所以尽量要少用