Q: oracle左外连接不显示空值

A: 添加放在on后面 还是where后面是主要原因 如果你的where条件不允许返回空值则不会有空值若想显示空值 ,可以把where后面的限制条件移动到on后面

eg:

select a.refnum, b.refnum, c.refnum
from myTable a 
left outer join myTable b on (a.contid = b.contid) 
left outer join myTable c on (a.contid = c.contid) 
where a.id_tp_cd = 90000
and b.id_tp_cd = 10000
and c.id_tp_cd = 20000

移动后

select a.refnum, b.refnum, c.refnum
from myTable a 
left outer join myTable b on (a.contid = b.contid and b.id_tp_cd = 10000) 
left outer join myTable c on (a.contid = c.contid and c.id_tp_cd = 20000) 
where a.id_tp_cd = 90000

Q: oracle使用不等号(《》)判断时空值不会显示

A 字段为null的时候,只能通过is null或者is not null来判断。

这样写才是正确的
select * from user where id <> '123' or id is null;

_注意:当使用or 和 and 拼接语句时,需要注意执行的先后顺序。where 后面如果有and,or的条件,则or自动会把左右的查询条件分开,即先执行and,再执行or。原因就是:and的执行优先级最高!_

eg:

逻辑运算的顺序
SQL1: select count(*) from tableA where DEL_FLAG = '0' or DEL_FLAG is null and 1 <> 1 
相当于
SQL1: select count(*) from tableA where DEL_FLAG = '0' or (DEL_FLAG is null and 1 <> 1)
当判断第一个条件DEL_FLAG = '0' 满足时就不再继续判断后面的条件了


SQL2: select count(*) from tableA where (DEL_FLAG = '0' or DEL_FLAG is null) and 1 <> 1
判断(DEL_FLAG = '0' or DEL_FLAG is null)这个条件的结果并且要同时满足后面的 and 1<>1
显然 1<>1 是false