oracle 外连接符号（+）的用法 .

 一、（+）和某一个值比较

现在有以下两张表：

              　　　　表A 员工（EMPLOYEE）

	EMP_NO	EMP_NAME	JOB	DEPT_NO
1	1	HXF	PRESIDENT	2
2	2	SCOTT	CLERK	1
3	3	SMITH	SALESMAN	3
4	4	JOHN	MANAGER

                                                       

                         表B 部门（DEPARTMENT）

	DEPT_NO	DEPT_NAME	LOCATION	DEL_FLG
1	1	ACCOUNTING	HANGZHOU	0
2	2	RESEARCH	BEIJING	0
3	3	OPERATIONS	SHANGHAI	1

 

其中部门表中字段DEL_FLG为0表示该条记录已删除，是无效记录；反之相反。
 

如果需要找出表A中的所有记录，并关联上表B中的有效记录，我们很可能会写出以下几种SQL语句：

  语句一：

SELECT A.EMP_NAME, A.JOB, B.*
 FROM EMPLOYEE A, DEPTMENT B
 WHERE A.DEPT_NO = B.DEPT_NO(+)
   AND B.DEL_FLG(+) = '0'

 

该语句利用Oracle的外连接符号，并用条件B.DEL_FLG(+) = '0'限定表B种的有效记录。也有人可能会写成语句二：

     语句二：

SELECT A.EMP_NAME, A.JOB, B.*
 FROM EMPLOYEE A, DEPTMENT B
 WHERE A.DEPT_NO = B.DEPT_NO(+)
   AND B.DEL_FLG = '0'

 

语句二中表B中的字段DEL_FlG没带外连接符号。

粗粗看起来，以上几种SQL语句的写法都有道理，没有问题。那我们再来看下它们的执行结果：

                            　　  语句一的执行结果

	EMP_NAME	JOB	DEPT_NO	DEPT_NAME	LOCATION	DEL_FLG
1	SCOTT	CLERK	1	ACCOUNTING	HANGZHOU	0
2	HXF	PRESIDENT	2	RESEARCH	BEIJING	0
3	JOHN	MANAGER
4	SMITH	SALESMAN

 

                                   语句二的执行结果

	EMP_NAME	JOB	DEPT_NO	DEPT_NAME	LOCATION	DEL_FLG
1	HXF	PRESIDENT	2	RESEARCH	BEIJING	0
2	SCOTT	CLERK	1	ACCOUNTING	HANGZHOU	0

 

我们发现几条语句的执行结果截然不同。那到底哪一个是对的呢？

首先我们应该清楚，要求是以表A为主表，表B为附表的外连接，根据外连接的定义，就要求选出表A中的所有记录，显然语句二和三的结果就不符号这个要求。其次，我们要求选出表B中的有效记录，出现在结果集中的表B的字段DEL_FLG的值只能是0。由此可见，语句一的执行结果是正确的。

    那么语句二是怎么回事呢？在语句二中，虽然对两张表中的相关字段进行了外连接，但是，对附表B中的字段DEL_FLG限定条件的时候，没有使用外连接符号，导致这个连接变成了全连接。

 

二、Oracle left join时，on和where的说明

　　在使用left jion时，on和where条件的区别如下：
　　1、on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
　　2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

三、（+） 和left join 的转换

select *
from eprk_person_account a, eprk_person_tax b
where a.pk_plan = b.pk_plan(+)
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 eprk_person_account a
left join eprk_person_tax b on a.pk_plan = b.pk_plan
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';/*给右表指定了条件*/