Oracle 空值判断详解（null）

1 概述

1. null 的意义
   (1) 表示 '未知' 或 '没有任何意义'
   (2) null 等同于 '' -- 请注意，'' 之间不能含有空格！
       
2. null 比较
   (1) 不能进行 '值比较'： =、<>  -- 无返回结果
   (2) 只能进行 '逻辑比较'：is null、is not null、exists、not exists
   
3. null 计算
   (1) 计算规则：null + 任何数 = null
   (2) null 计算时，建议先判空，如：nvl、nvl2、decode

2 示例

2.1 = 、<>

with t_test as (
  select 1 id, null name from dual union all
  select 2 id, ''   name from dual union all -- 也是 null
  select 3 id, ' '  name from dual union all -- 有值 = 空格
  select 4 id, 'a'  name from dual union all
  select 5 id, 'b'  name from dual
)

select * from t_test t where t.name = null
union all
select * from t_test t where t.name = '';

测试结果：null 无法用 <>、= 进行运算

2.2 is null、is not null

with t_test as (
  select 1 id, null name from dual union all
  select 2 id, ''   name from dual union all
  select 3 id, ' '  name from dual union all
  select 4 id, 'a'  name from dual union all
  select 5 id, 'b'  name from dual
)

select * from t_test t where t.name is null;

测试结果：

2.3 nvl、nvl2、decode

with t_test as (
  select 1 id, null name from dual union all
  select 2 id, ''   name from dual union all
  select 3 id, ' '  name from dual union all
  select 4 id, 'a'  name from dual union all
  select 5 id, 'b'  name from dual
)

select * from t_test t where nvl(t.name, 'x') = 'x';
-- select * from t_test t where nvl2(t.name, 'c', 'x') = 'x';
-- select * from t_test t where decode(t.name, '', 'x') = 'x';

测试结果：

3 扩展

3.1 值比较：in、not in

-- in：满足任意一个
-- not in：必须全部满足 （只有存在 null，都返回 false，所以尽量避免使用）
-- 说明：true: 有返回值， false: 无返回值
select 'false' c1 from dual where 1 in (2, null);
select 'true'  c2 from dual where 1 in (2, null, 1);

select 'fasle' c3 from dual where 1 not in (2, null);
select 'fasle' c4 from dual where 1 not in (2, null, 1);

select 'false' c5 from dual where null not in (1, 2);

3.2 逻辑比较：exists、not exists

with t_test as (
  select 1 id, null name from dual union all
  select 2 id, ''   name from dual union ALL
  select 3 id, ' '  name from dual union ALL
  select 4 id, 'a'  name from dual union all
  select 5 id, 'b'  name from dual
)
select t1.*
  from t_test t1
 where exists (select 'x'
                 from t_test t2
                where t2.id = t1.id
                  and t2.name is not null);

测试结果：