首先null值不会分配空间，这一点与''是本质区别，但是表现形式是一样的
首先null值与任何值做布尔运算，结果都是false
null值判断，is null,is not null
hank=> create table tab1 (a int ,b varchar(20));
CREATE TABLE
hank=> insert into tab1 values(1,'hank');
INSERT 0 1
hank=> insert into tab1 values(1,null);
INSERT 0 1
hank=> insert into tab1 values(1,'askasdjk');
INSERT 0 1
hank=> select * from tab1 where b <>'hank';
a | b
---+----------
1 | askasdjk
(1 row)
可见b为null的值是不被筛选的
hank=> insert into tab1 values(1,'');
INSERT 0 1
hank=> select * from tab1 where b <>'hank';
a | b
---+----------
1 | askasdjk
1 |
''是可以被筛选的
要想达到效果，可以用如下写法
select * from tab1 where b <>'hank' or b is null;
a | b
---+----------
1 |
1 | askasdjk
1 |
(3 rows)
hank=> select * from tab1 where coalesce(b,'0')<>'hank';
a | b
---+----------
1 |
1 | askasdjk
1 |
(3 rows)
null值排序
hank=> select ctid,* from tab1 where b <>'hank' or b is null order by b nulls first;
ctid | a | b
-------+---+----------
(0,2) | 1 |
(0,4) | 1 |
(0,3) | 1 | askasdjk
hank=> select ctid,* from tab1 where b <>'hank' or b is null order by b nulls last;
ctid | a | b
-------+---+----------
(0,4) | 1 |
(0,3) | 1 | askasdjk
(0,2) | 1 |
(3 rows)
null值排序对索引的影响
null列索引注意一定要一致，否则索引用不到
insert into tab1 select generate_series(1,1000),'hank'||generate_series(1,1000);
hank=> create index idx_tab1_b on tab1(b nulls first);
hank=> explain select * from tab1 order by b limit 1;
QUERY PLAN
---------------------------------------------------------------------
Limit (cost=21.06..21.06 rows=1 width=11)
-> Sort (cost=21.06..23.57 rows=1004 width=11)
Sort Key: b
-> Seq Scan on tab1 (cost=0.00..16.04 rows=1004 width=11)
(4 rows)
hank=> explain select * from tab1 order by b nulls first limit 1;
QUERY PLAN
----------------------------------------------------------------------------------
Limit (cost=0.28..0.32 rows=1 width=11)
-> Index Scan using idx_tab1_b on tab1 (cost=0.28..49.21 rows=1004 width=11)
hank=> explain select * from tab1 order by b nulls last limit 1;
QUERY PLAN
---------------------------------------------------------------------
Limit (cost=21.06..21.06 rows=1 width=11)
-> Sort (cost=21.06..23.57 rows=1004 width=11)
Sort Key: b
-> Seq Scan on tab1 (cost=0.00..16.04 rows=1004 width=11)
(4 rows)
可见，null值得先后可以决定索引是否可以用到idx_tab1_b这个索引，需要重新排序
hank=> create index idx_tab1_b_1 on tab1(b);
CREATE INDEX
hank=> explain select * from tab1 order by b;
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using idx_tab1_b_1 on tab1 (cost=0.28..49.21 rows=1004 width=11)
(1 row)
hank=> explain select * from tab1 order by b desc;
QUERY PLAN
---------------------------------------------------------------------------------------
Index Scan Backward using idx_tab1_b_1 on tab1 (cost=0.28..49.21 rows=1004 width=11)
(1 row)
hank=> explain select * from tab1 order by b asc;
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using idx_tab1_b_1 on tab1 (cost=0.28..49.21 rows=1004 width=11)
(1 row)
不涉及null值排序的索引不会出现此种情况
所以有空值的字段如果对null值有排序需求，那么建索引的时候一定要按筛选条件建立。