Oracle 限制时将空值排除

1.Oracle特性

Oracle有个特性,就是在限制时,会将空值也排除在外:
例如:

select '小黑'name,'2'class from dual
union all select '小白'name,'0'class from dual
union all select '小红'name,'1'class from dual
union all select '小绿'name,'4'class from dual
union all select null name,'5' class from dual

如果排除小黑,那么会得到这样的数据:

select * from (select '小黑'name,'2'class from dual
union all select '小白'name,'0'class from dual
union all select '小红'name,'1'class from dual
union all select '小绿'name,'4'class from dual
union all select null name,'5' class from dual)where name<>'小黑'

数据库在排除小黑这条数据的同时,也将class为5的数据也排除掉了

2.解决

问题也很好解决,只需要在限制后面添加or name is null,即可:

select * from (select '小黑'name,'2'class from dual
union all select '小白'name,'0'class from dual
union all select '小红'name,'1'class from dual
union all select '小绿'name,'4'class from dual
union all select null name,'5' class from dual)where name<>'小黑' or name is null