南大通用GBase 8a查询空值记录，null值等的方法

本文介绍在GBase 8a数据库中，如何查询空值。这里空值有2个描述：1、长度为0的字符串，2、null值。

数据

如下样例构造了3行数据，包括1行null,一个''的空值。

gbase> create table tt1(id int,name varchar(100));
Query OK, 0 rows affected (Elapsed: 00:00:01.20)

gbase> insert into tt1 values(1,'First'),(2,''),(3,null);
Query OK, 3 rows affected (Elapsed: 00:00:00.36)
Records: 3  Duplicates: 0  Warnings: 0

gbase> select * from tt1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | First |
|    2 |       |
|    3 | NULL  |
+------+-------+
3 rows in set (Elapsed: 00:00:00.01)

通过is null 查询是否为null

gbase> select * from tt1 where name is null;
+------+------+
| id   | name |
+------+------+
|    3 | NULL |
+------+------+
1 row in set (Elapsed: 00:00:00.00)

通过is not null 查询非null的

gbase> select * from tt1 where name is not null;
+------+-------+
| id   | name  |
+------+-------+
|    1 | First |
|    2 |       |
+------+-------+
2 rows in set (Elapsed: 00:00:00.01)

通过=''查询空的字符串

gbase> select * from tt1 where name='';
+------+------+
| id   | name |
+------+------+
|    2 |      |
+------+------+
1 row in set (Elapsed: 00:00:00.00)

通过组合条件查询空字符串和null

gbase> select * from tt1 where name is null or name='';
+------+------+
| id   | name |
+------+------+
|    2 |      |
|    3 | NULL |
+------+------+
2 rows in set (Elapsed: 00:00:00.02)

通过nvl函数查询空字符串和null

gbase> select * from tt1 where nvl(name,'')='';
+------+------+
| id   | name |
+------+------+
|    2 |      |
|    3 | NULL |
+------+------+
2 rows in set (Elapsed: 00:00:00.01)

参考

GBase 8a对null相关函数nvl，ifnull,nullif等