本文介绍在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)