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 效率上不如<>,=所以尽量要少用