ORACLE专题-外连接过滤
一、概述
在ANSI SQL-92标准的SQL中,外连接分为left outer join左外连接,right outer join 右外连接,full outer join全外连接,在oracle9i之前用(+)支持。从oracle9i之后,既可以使用原有的(+)外连接符合,也可以使用ANSI SQL-92标准的外连接 。在使用外连接的时候,用where子句和and条件去做过滤限制的时候,需要注意语句的逻辑是否符合我们的预期 。
二 、ANSI SQL-92标准外连接
1.用where条件去限制
在这种情况下,oracle会按照连接条件去做连接,对于不符合过滤条件的记录,直接过滤掉 。
当然,oracle优化器再处理这种情况的时候,在不改变sql功能的情况下,可能会在连接前先应用where条件过滤一些数据,以此来提高sql效率 。
SQL> INSERT INTO DEPT (DEPTNO, DNAME) VALUES (99, 'test');
SQL> COMMIT;
SQL> SELECT EMPNO, A.DEPTNO, B.DEPTNO, B.DNAME
2 FROM EMP A
3 LEFT JOIN DEPT B
4 ON A.DEPTNO = B.DEPTNO
5 WHERE B.DEPTNO = 99;
EMPNO DEPTNO DEPTNO DNAME
--------- ------ ------ --------------
如果把上面sql的 where子句改为对A表的限制,即where a.deptno=99,仍查询不到结果 。
通过执行计划可以看到,oracle首先通过索引访问dept,并且限制了条件b.deptno=99,然后对emp表做全表扫描,也就是先应用where条件,然后再去做表与表的关联操作.这样看来,如果过滤条件放在where子句中,是不是oracle就是先应用where过滤,然后再做连接操作,是不是完全这样的呢?再看下面的这个例子:
SQL> SELECT A.DEPTNO, A.DNAME, B.EMPNO, B.ENAME
2 FROM DEPT A
3 LEFT JOIN EMP B
4 ON A.DEPTNO = B.DEPTNO
5 WHERE B.DEPTNO IS NULL;
DEPTNO DNAME EMPNO ENAME
------ -------------- --------- ----------
99 test
这个sql的执行计划清楚的表明,oracle是先把A,B表的记录都取出来,然后做连接,最后才去filter过滤 。
可以看到,用where去限制的话,oracle可能先过滤再连接,也有可能先连接再过滤 。
2.用and条件去限制
把where子句的 b.deptno=99改为join 中的and b.deptno=99 SQL> SELECT EMPNO, A.DEPTNO, B.DEPTNO, B.DNAME
2 FROM EMP A
3 LEFT JOIN DEPT B
4 ON A.DEPTNO = B.DEPTNO
5 AND B.DEPTNO = 99;
EMPNO DEPTNO DEPTNO DNAME
--------- ------ ------ --------------
7370 20
7369 20
7499 30
……
再来看看对A表的deptno做限制:
SQL> SELECT EMPNO, A.DEPTNO, B.DEPTNO, B.DNAME
2 FROM EMP A
3 LEFT JOIN DEPT B
4 ON A.DEPTNO = B.DEPTNO
5 AND A.DEPTNO = 10;
EMPNO DEPTNO DEPTNO DNAME
--------- ------ ------ --------------
7566 20
7654 30
7698 30
7782 10 10 ACCOUNTING
……
可以看到,在访问A表的时候并没有用过滤条件,取出的总的纪录数还是和A表纪录数一致.
其实,当deptno的过滤条件放在join子句中的时候, b.deptno=条件为任何值,结果都会出所有纪录,因为此时相当于告诉oracle,我要先对A表全表扫描方式访问A表所有纪录,然后再去和B表做左关联,关联上的话,就把从表对应字段的值填上,关联不上的就置空值。
3.right join
right join的情况和left join类似 。
4.总结
当在内连接查询中加入条件时,无论是将它加入到join子句,还是加入到where 子句,其效果是完全一样的,但对于外连接情况就不同了,总结如下:
(1)当把条件加入到join子句时,oracle首先对相关表进行连接,然后再对连接
结果做过滤操作,符合条件的纪录会返回完整的记录结果,不符合过滤条件的,
从表相应的字段置为空,总之,sql出来的纪录数肯定是和连接结果集的纪录
数一致。
(2)如果将条件放到where子句中,oracle可能会先对相应表应用where子句,
进行筛选,然后进行连接操作,也可能会在做完表与表的连接后,再来对结果
集合做筛选。
三 、传统的(+)外连接
根据emp,dept表的信息,查找所有emp信息,如果dept.deptno=10,则把dname 取出来,其他的就为null,用外连接操作,以emp表作为主表:
SQL> SELECT EMPNO, A.DEPTNO, B.DEPTNO, B.DNAME
2 FROM EMP A, DEPT B
3 WHERE A.DEPTNO = B.DEPTNO(+)
4 AND B.DEPTNO = 10;
EMPNO DEPTNO DEPTNO DNAME
--------- ------ ------ --------------
7782 10 10 ACCOUNTING
7839 10 10 ACCOUNTING
7934 10 10 ACCOUNTING
这个sql的执行结果并不是我们想要的,丢失了deptno＜>10的记录,从执行计划来看,oracle 自动对A表也加了filter,然后对A,B表做内关联 。
将sql语句改为如下:
SQL> SELECT EMPNO, A.DEPTNO, B.DEPTNO, B.DNAME
2 FROM EMP A, DEPT B
3 WHERE A.DEPTNO = B.DEPTNO(+)
4 AND B.DEPTNO(+) = 10;
EMPNO DEPTNO DEPTNO DNAME
--------- ------ ------ --------------
7934 10 10 ACCOUNTING
7839 10 10 ACCOUNTING
7782 10 10 ACCOUNTING
7900 30
……
再来看下面的例子:
SQL> SELECT EMPNO, B.DEPTNO, A.DEPTNO, A.DNAME
2 FROM EMP B, DEPT A
3 WHERE A.DEPTNO = B.DEPTNO(+)
4 AND B.DEPTNO IS NULL;
EMPNO DEPTNO DEPTNO DNAME
--------- ------ ------ --------------
99 test
根据以上执行计划,可以看到,过滤放在了连接操作后 。
总结:
(1)如果有类似于and a.col1=xxx这样的限制条件,其功能和处理方式,和ANSI
SQL-92标准中限制条件放在where子句的情况相同,即可能会先对相应表应用
where子句,进行筛选,然后进行连接操作;也可能会在做完表与表的连接后,
再来对结果集合做筛选 。
(2)如果是类似于and a.col1(+)=xxx,则oracle会先做外连接,然后再在外连接
的基础上做过滤,如果不符合过滤条件,只是相应字段为空;如果符合,则置
相应的值,记录数在此并不会被过滤掉 。相对应于ANSI SQL-92标准中限制条
件放在join子句的情况 。
。
