空值介绍

NULL是用于表示缺失值的术语。表中的NULL值是字段中显示为空白的值。NULL值的字段是没有值的字段。

本页总结了Hubble中值为NULL的处理方式。

使用内置客户端时,空值显示值NULL。这将它们与包含空字符串 ("") 的字符字段区分开来。

NULL简单比较

NULL值和结果之间的任何简单比较。

create table tb(
  a int2,
  b int4,
  c int8
);
insert into tb values(1, 0, 0);
insert into tb values(2, 0, 1);
insert into tb values(3, 1, 0);
insert into tb values(4, 1, 1);
insert into tb values(5, NULL, 0);
insert into tb values(6, NULL, 1);
insert into tb values(7, NULL, NULL);
select * from tb;
+---+------+------+
| a |  b   |  c   |
+---+------+------+
| 1 |    0 |    0 |
| 2 |    0 |    1 |
| 3 |    1 |    0 |
| 4 |    1 |    1 |
| 5 | NULL |    0 |
| 6 | NULL |    1 |
| 7 | NULL | NULL |
+---+------+------+
select * from tb where b is null and c is not null;
| a |  b   | c |
+---+------+---+
| 5 | NULL | 0 |
| 6 | NULL | 1 |
+---+------+---+

NULL和条件运算符

条件运算符(包括IF, COALESCE)仅根据IFNULL条件操作数的值评估某些操作数,它们的结果并不总是NULL取决于给定的操作数。

例如,COALESCE(1, NULL)也将返回1

SELECT COALESCE(1, NULL);
  coalesce
------------
     1

NULL和算术

涉及NULL值的算术运算将产生NULL结果。

select a, b, c, b*0, b*c, b+c from tb;
| a |  b   |  c   | b * 0 | b * c | b + c |
+---+------+------+-------+-------+-------+
| 1 |    0 |    0 |     0 |     0 |     0 |
| 2 |    0 |    1 |     0 |     0 |     1 |
| 3 |    1 |    0 |     0 |     0 |     1 |
| 4 |    1 |    1 |     0 |     1 |     2 |
| 5 | NULL |    0 | NULL  | NULL  | NULL  |
| 6 | NULL |    1 | NULL  | NULL  | NULL  |
| 7 | NULL | NULL | NULL  | NULL  | NULL  |
+---+------+------+-------+-------+-------+

NULL和聚合函数

聚合函数是对一组行进行操作并返回单个值的函数。为了更容易理解结果,此处重复了示例数据。

select * from tb;
 a |  b   |  c
----+------+-------
  1 |    0 |    0
  2 |    0 |    1
  3 |    1 |    0
  4 |    1 |    1
  5 | NULL |    0
  6 | NULL |    1
  7 | NULL | NULL
select COUNT(*), COUNT(b), SUM(b), AVG(b), MIN(b), MAX(b) from tb;
  count | count | sum | avg | min | max
--------+-------+-----+-----+-----+------
      7 |     4 |   2 | 0.5 |   0 |   1

事项说明:

  • NULL值不包含在COUNT()列中。COUNT(*)返回7而COUNT(b)返回4。

  • NULL值不被视为其中的最大值MIN()或最小值MAX()

  • AVG(b)在返回时候也不考虑空值作为有效值。

NULL作为不同的值

NULL值被认为与其他值不同,并包含在列中的不同值列表中。

select distinct c from tb;
  c
--------
     0
     1
  NULL

但是,计算不同值的数量不包括NULL,这与COUNT()函数一致。

select count(distinct c) from tb;
| count(distinct c) |
+-------------------+
|                 2 |
+-------------------+

NULL作为其他值

在某些情况下,如果希望NULL在算术或聚合函数计算中包含值。为此,请在计算期间使用该IFNULL()函数替换一个空值。

例如,假设想要计算列的平均值,cSUM()所有数字c除以总行数,而不管c的值是否为NULL。在这种情况下,将使用AVG(IFNULL(c, 0)), 其中在计算期间用IFNULL(c, 0)NULL值替换为0

select avg(c) as m, avg(ifnull(c,0)) as n from tb;
  m   |          n
------+-------------------------
  0.5 | 0.42857142857142857143

NULL和集合操作

NULL值被视为UNION集合操作的一部分。

select c from tb union select c from tb;
   c
--------
   0
   1
  NULL

NULL排序

在对值的列进行排序包含NULL时,当进行升序时候首先进行null排序,后进行列值比较:

select * from tb order by c asc;
  a |  b   |  c
----+------+-------
  7 | NULL | NULL
  3 |    1 |    0
  5 | NULL |    0
  1 |    0 |    0
  2 |    0 |    1
  4 |    1 |    1
  6 | NULL |    1

当进行降序时候先行列值比较,而后展示null

select * from tb order by c desc;
  a |  b   |  c
----+------+-------
  2 |    0 |    1
  4 |    1 |    1
  6 | NULL |    1
  1 |    0 |    0
  3 |    1 |    0
  5 | NULL |    0
  7 | NULL | NULL

NULL与唯一约束

NULL值不被认为是唯一的。因此,如果一个表对一个或多个可选列具有约束,则可以在这些列中插入具有值为NULL的多行,如下例所示

create table tt(m int, n int unique);
insert into tt values(1, 1);
insert into tt values(3, null);
insert into tt values(5, null);
select * from tt;
  m |  n
----+-------
  1 |    1
  3 | NULL
  5 | NULL

NULL和CHECK约束

计算结果为的CHECK约束表达式NULL被认为是通过,例如a < b而不用担心添加OR a IS NULL子句。当需要非空验证时,通常的NOT NULL约束可以与CHECK约束一起使用。

create table shops (id string primary key, b int not null check (b > 0), a int, check (a < b));
insert into shops (id, b) values ('nccdd4477', -5);
failed to satisfy CHECK constraint (b > 0:::INT8)
insert into shops values ('nccdffh87', 5, 13);
failed to satisfy CHECK constraint (a < b)

NULL与其他类型的连接

NULL值和NULL值之间的连接会产生一个NULL值。

select NULL || 1 as t;
   t
--------
  NULL
select null || 'qwe' as t;
   t
--------
  NULL