is [not] distinct from 的 null 比较

简介: --在postgresql中经常会比较两个值相同或者不同,但如果比较表达中有一个为null则会比较头痛,如下:postgres=# select null null ,null = n...


--在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 效率上不如<>,=所以尽量要少用
目录
相关文章
|
9月前
|
存储 SQL 关系型数据库
1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column的解决办法
1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column的解决办法
96 0
|
SQL 分布式计算 Spark
SPARK Expand问题的解决(由count distinct、group sets、cube、rollup引起的)
SPARK Expand问题的解决(由count distinct、group sets、cube、rollup引起的)
605 0
SPARK Expand问题的解决(由count distinct、group sets、cube、rollup引起的)
ORDER BY子句
ORDER BY子句
40 0
|
SQL
ORDER BY && GROUP BY
ORDER BY && GROUP BY
70 0
ORDER BY && GROUP BY
|
SQL 关系型数据库 MySQL
【已解决】SELECT list is not in GROUP BY clause and contains nonaggregated column
MySQL5.7.5后only_full_group_by成为sql_mode的默认选项之一,这可能导致一些sql语句失效。
199 0
Distinct
SQL 去重
120 0
|
SQL 关系型数据库 MySQL
报错:[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregat
报错:[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregat
299 0
报错:[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregat
|
存储 .NET C#
C# LINQ 详解 From Where Select Group Into OrderBy Let Join
目录 1. 概述 2. from子句 3. where子句 4. select子句 5. group子句 6. into子句 7. 排序子句 8. let子句 9. join子句 10. 小结 1. 概述     LINQ的全称是Language Integrated Query,中文译成“语言集成查询”。
2003 0
|
SQL 索引
SQL去重是用DISTINCT好,还是GROUP BY好?
SQL去重是用DISTINCT好,还是GROUP BY好?
SQL去重是用DISTINCT好,还是GROUP BY好?
|
SQL 关系型数据库 MySQL
select、distinct、limit使用
select、distinct、limit使用
234 0
select、distinct、limit使用