select *
from (select a.custom_no, b.debit_ac
from custom a
left join custdep b
on a.custom_no = b.custom_no
and b.dep_no = 'EE') t
where t.debit_ac is null;select a.custom_no, b.debit_ac
from custom a
left join custdep b
on a.custom_no = b.custom_no
and b.dep_no = 'EE'
and b.debit_ac is null;
这两条语句什么区别,结果差异很大。
select a.custom_no, b.debit_ac
from custom a
left join custdep b
on a.custom_no = b.custom_no
and b.dep_no = 'EE'
and b.debit_ac is null;可以改写为
select a.custom_no, b.debit_ac
from custom a ,custdep b
where a.custom_no = b.custom_no(+)
and b.dep_no = 'EE'
and b.debit_ac is null;你在看一下.
(select a.custom_no, b.debit_ac
from custom a
left join custdep b
on a.custom_no = b.custom_no
and b.dep_no = 'EE')
里找出满足条件where t.debit_ac is null;的结果集。第二个select语句从表custom a 和custdep b 找出满足条件
a.custom_no = b.custom_no
and b.dep_no = 'EE'
and b.debit_ac is null;
的结果集。
left join on 后面的条件是连接数据,
where 条件是过滤数据
假如custom有数据,而custdep没有数据,最后的会显示custom的数据和值为NULL的debit_ac
第二条语句:
假如custom有数据,而custdep没有数据,则不会显示此笔数据
from (select a.custom_no, b.debit_ac
from custom a
left join custdep b
on a.custom_no = b.custom_no
and b.dep_no = 'EE') t
where t.debit_ac is null;
重点是找出t.debit_ac is null 根据这个条件可能把原a表中的的给过滤掉select a.custom_no, b.debit_ac
from custom a
left join custdep b
on a.custom_no = b.custom_no
and b.dep_no = 'EE'
and b.debit_ac is null;
而这个不管b.debit_ac is null a表中的数据行都会保留
在2个表left join 之后 在对结果集用t.debit_ac 进行筛选第二个是2个表的连接条件 满足a.custom_no = b.custom_no
and b.dep_no = 'EE'
and b.debit_ac is null
这3个条件的对应结果才能被连接