总结1:oracal中认为null 与 "" 是相同的。
验证如下:
select (case when '' is null then 'true' else 'false' end) as result from dual; --true
select (case when trim(' ') is not null then 'not null' else 'is null' end) as age from dual; --true
select (case when (' ') is not null then 'not null' else 'is null' end) as age from dual; --false
总结2:在条件过滤中,oracal字段中如果值为“”或者null 不能通过= ,!= , <>, in() ,not in()等进行过滤,写条件过滤的时候应该特别注意!避免漏掉值为空的情况
测试如下:
select (case when ''='' then 'true' else 'false' end) as result from dual;--false
select (case when '' !='01' then 'true' else 'false' end) as result from dual;--false
select (case when '' <>'01' then 'true' else 'false' end) as result from dual;--false
select (case when '' in('',null) then 'true' else 'false' end) as result from dual;--false
select (case when null in('',null) then 'true' else 'false' end) as result from dual;--false
select (case when '' not in('01') then 'true' else 'false' end) as result from dual;--false
例如
user 表中有三条记录
userid usename
1 u1
2
3 u3
查询用户名不是u1的数据
select * from user u where u.username <> 'u1' ;-- 这样讲只返回第三条,第二条没有在查询范围内。
正确写法:
select * from user u where u.username is null or u.username <> 'u1' ;