Oracle left join时，on和where的说明

一、问题描述

　　发现以下SQL的执行结果不正确，应返回3条记录，实际只返回1条记录：

select *

from xxxx_person_account a, xxxx_person_tax b

where a.pk_plan = b.pk_plan(+)

  and a.pk_corporation = b.pk_corporation

  and a.pk_person = b.pk_person(+)

  and a.pk_fund = b.pk_fund(+)

  and a.pk_accounttype = b.pk_accounttype(+)

  and a.pk_corporation = '0001AA10000000014PGN'

  and b.pk_corporation = '0001AA10000000014PGN'; /*给右表指定了条件*/

　　上述SQL等价于以下SQL：

select *

from xxxx_person_account a

left join xxxx_person_tax b on a.pk_plan = b.pk_plan

  and a.pk_corporation = b.pk_corporation

  and a.pk_person = b.pk_person

  and a.pk_fund = b.pk_fund

  and a.pk_accounttype = b.pk_accounttype

where a.pk_corporation = '0001AA10000000014PGN'

  and b.pk_corporation = '0001AA10000000014PGN';/*给右表指定了条件*/

　　其中左表中应有3条记录，右表中应有1条记录。

　　看起来，Oracle自动把left join转换成inner join了，从执行计划上看也证明了这一点。

 

二、问题分析

　　从网上找资料，发现以下描述：

　　在使用left jion时，on和where条件的区别如下：

　　1、on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

　　2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

　　on后的条件用来生成左右表关联的临时表，where后的条件对临时表中的记录进行过滤。

　　从上例上看，生成临时表后，使用where条件中的b.pk_corporation对临时表中的记录进行过滤时，3条记录中，只有1条满足条件，所以只返回了1条记录。

　　问题的关键在于：在where条件中，b.pk_corporation =  '0001AA10000000014PGN'条件，是iner join的写法，left join的写法，应为b.pk_corporation(+) =  '0001AA10000000014PGN'。

 

三、问题总结

　　使用left join时，对于右表的(关联字段)条件，建议写在on后，或使用left join的方式指定，或使用子查询的方式改造。

　　1、写在on后：

select *

from xxxx_person_account a

left join xxxx_person_tax b on a.pk_plan = b.pk_plan

  and a.pk_corporation = b.pk_corporation

  and a.pk_person = b.pk_person

  and a.pk_fund = b.pk_fund

  and a.pk_accounttype = b.pk_accounttype

  and b.pk_corporation = '0001AA10000000014PGN'

where a.pk_corporation = '0001AA10000000014PGN';

　　2、使用left join的方式指定：

select *

from xxxx_person_account a, xxxx_person_tax b

where a.pk_plan = b.pk_plan(+)

  and a.pk_corporation = b.pk_corporation(+)

  and a.pk_person = b.pk_person(+)

  and a.pk_fund = b.pk_fund(+)

  and a.pk_accounttype = b.pk_accounttype(+)

  and a.pk_corporation = '0001AA10000000014PGN'

  and b.pk_corporation(+) = '0001AA10000000014PGN';

　　3、使用子查询改造

select *

from xxxx_person_account a, (select * from xxxx_person_tax b where b.pk_corporation = '0001AA10000000014PGN') b

where a.pk_plan = b.pk_plan(+)

  and a.pk_corporation = b.pk_corporation(+)

  and a.pk_person = b.pk_person(+)

  and a.pk_fund = b.pk_fund(+)

  and a.pk_accounttype = b.pk_accounttype(+)

  and a.pk_corporation = '0001AA10000000014PGN';

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26802046/viewspace-719069/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26802046/viewspace-719069/