关于Oracle 的NULL

原本设想 两数相加

select a+b from (
  select 11 A, 22 B from dual
)

结果: 33

但是如果其中一个数为NULL时

select a+b from (
  select null A, 22 B from dual
)

结果: null
没有得到我们预期的结果: 22

正确的写法:

select nvl(a,0)+nvl(b,0) from (
  select null A, 22 B from dual
)

结果: 22

NULL参加统计

select count(A) from (
  select 1 A from dual
  union
  select 2 A from dual
  union
  select 3 A from dual
  union
  select 4 A from dual
)

结果: 4

如果统计的数据中有NULL值：

select count(A) from (
  select 1 A from dual
  union
  select 2 A from dual
  union
  select null A from dual
  union
  select 4 A from dual
)

结果: 3

显然没有达到我们计数统计的需求
应该写成这样:

select count(decode(A, null, 1, 1)) from (
  select 1 A from dual
  union
  select 2 A from dual
  union
  select null A from dual
  union
  select 4 A from dual
)

结果: 4