外连接分为left outer join左外连接，right outer join右外连接,full outer join全外连接，oracle9i之前用(+)去支持，而ANSI SQL-92标准的sql用的是left/right/full outer join关键字，从oracle9i之后，我们既可以使用原有的(+)外连接符合，也可以使用ANSI SQL-92标准的外连接。需要提醒的是，在使用外连接的时候，用where子句和and条件去做过滤限制的时候,需要注意语句的逻辑是否符合我们的预期,总结如下:
如果是ANSI SQL-92标准的外连接语句
(1) 当在内连接查询中加入条件时，无论是将它加入到join子句，还是加入到where子句，其效果是完全一样的，但对于外连接情况就不同了。
(2) 当把条件加入到join子句时，oracle首先对相关表进行连接,然后再对连接结果做过滤操作,符合条件的纪录会返回完整的记录结果,不符合过滤条件的,从表相应的字段置为空,总之,sql出来的纪录数肯定是和连接结果集的纪录数一致。
(3) 如果将条件放到where子句中，oracle优化器可能会先对相应表应用where子句，进行筛选,然后进行连接操作，也可能会在做完表与表的连接后，再来对结果集合做筛选。你可以理解为oracle会先做连接，然后对连接的结果集做过滤，过滤后的记录数可能会减少，和放在join区别是: where语句会过滤掉不符合条件的记录，sql出来的记录数要比连接结果集的纪录数少，join不会改变连接的结果集记录数。
如果是oracle传统的(+)外连接语句
(1) 在传统的(+)操作中，如果有类似于and a.col1=xxx这样的限制条件，其功能和处理方式，和ANSI SQL-92标准中限制条件放在where子句的情况相同。
(2) 如果是类似于and a.col1(+)=xxx,则oracle会先做外连接，然后再在外连接的基础上做过滤，如果不符合过滤条件，只是相应字段为空，如果符合，则置相应的值，记录数在此并不会被过滤掉。相对应于ANSI SQL-92标准中限制条件放在join子句的情况。
ANSI SQL-92：
用where条件去限制
在这种情况下，oracle会按照连接条件去做连接，对于不符合过滤条件的记录，直接过滤掉。当然，oracle优化器再处理这种情况的时候，在不改变sql功能的情况下，可能会在连接前先应用where条件过滤一些数据，以此来提高sql效率
SQL> insert into dept(deptno,dname) values (99,'test');
SQL> commit;
左连接left join
SQL> select empno,a.deptno,b.deptno,b.dname from emp a left join dept b on a.deptno=b.deptno
2 where b.deptno=99;
未选定行
执行计划
1 Plan hash value: 568005898
2
3 ----------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 ----------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 1 | 19 | 4 (0)| 00:00:01 |
7 | 1 | NESTED LOOPS | | 1 | 19 | 4 (0)| 00:00:01 |
8 | 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 12 | 1 (0)| 00:00:01 |
9 |* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
10 |* 4 | TABLE ACCESS FULL | EMP | 1 | 7 | 3 (0)| 00:00:01 |
11 ----------------------------------------------------------------------------------------
12
13 Predicate Information (identified by operation id):
14 ---------------------------------------------------
15
16 3 - access("B"."DEPTNO"=99)
17 4 - filter("A"."DEPTNO"=99)
如果把上面的sql where该为对a表的限制即where a.deptno=99
1 Plan hash value: 1301846388
2
3 ----------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 ----------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 1 | 19 | 4 (0)| 00:00:01 |
7 | 1 | NESTED LOOPS OUTER | | 1 | 19 | 4 (0)| 00:00:01 |
8 |* 2 | TABLE ACCESS FULL | EMP | 1 | 7 | 3 (0)| 00:00:01 |
9 | 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 12 | 1 (0)| 00:00:01 |
10 |* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
11 ----------------------------------------------------------------------------------------
12
13 Predicate Information (identified by operation id):
14 ---------------------------------------------------
15
16 2 - filter("A"."DEPTNO"=99)
17 4 - access("B"."DEPTNO"(+)=99)
可以看到,oracle首先通过索引访问dept,并且限制了条件b.deptno=99,然后对emp表做全表扫描的时候,自动加了filter,也就是先应用where条件,然后再去做表与表的关联操作.这样看来，如果过滤条件放在where子句中，是不是oracle就是先应用where过滤，然后再做连接操作，是不是完全这样的呢？再看下面的这个例子
SQL> select a.deptno,a.dname,b.empno,b.ename from deptbak a left join empbak b on a.deptno=b.deptno where b.deptno is null;
DEPTNO DNAME EMPNO ENAME
------ -------------- ----- ----------
99 test
40 OPERATIONS
1 Plan hash value: 241861675
2
3 -------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 -------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 14 | 280 | 7 (15)| 00:00:01 |
7 |* 1 | FILTER | | | | | |
8 |* 2 | HASH JOIN OUTER | | 14 | 280 | 7 (15)| 00:00:01 |
9 | 3 | TABLE ACCESS FULL | DEPTBAK | 5 | 50 | 3 (0)| 00:00:01 |
10 | 4 | TABLE ACCESS FULL | EMPBAK | 14 | 140 | 3 (0)| 00:00:01 |
11 -------------------------------------------------------------------------------
12
13 Predicate Information (identified by operation id):
14 ---------------------------------------------------
15
16 1 - filter("B"."DEPTNO" IS NULL)
17 2 - access("A"."DEPTNO"="B"."DEPTNO"(+))
这个sql的执行计划清楚的表明，oracle是先把a，b表的记录都取出来，然后做hash连接，最后才去filter。
可以看到，用where去限制的话，oracle可能先过滤再连接，也有可能先连接再过滤。
用and条件去限制
把where b.deptno=99改为join 中的and b.deptno=99
SQL> select empno,a.deptno,b.deptno,b.dname from emp a left join dept b on a.deptno=b.deptno
2 and b.deptno=99;
EMPNO DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
7934 10
7839 10
7782 10
7900 30
7844 30
7698 30
7654 30
7521 30
7499 30
7902 20
7876 20
EMPNO DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
7788 20
7566 20
7369 20
1 Plan hash value: 657582733
2
3 ----------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 ----------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 14 | 266 | 5 (20)| 00:00:01 |
7 |* 1 | HASH JOIN OUTER | | 14 | 266 | 5 (20)| 00:00:01 |
8 | 2 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 |
9 | 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 12 | 1 (0)| 00:00:01 |
10 |* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
11 ----------------------------------------------------------------------------------------
12
13 Predicate Information (identified by operation id):
14 ---------------------------------------------------
15
16 1 - access("A"."DEPTNO"="B"."DEPTNO"(+))
17 4 - access("B"."DEPTNO"(+)=99)
再来看看对a表的deptno做限制
SQL> select empno,a.deptno,b.deptno,b.dname from emp a left join dept b on a.deptno=b.deptno and a.deptno=10;
EMPNO DEPTNO DEPTNO DNAME
----- ------ ------ --------------
7369 20
7499 30
7521 30
7566 20
7654 30
7698 30
7782 10 10 ACCOUNTING
7788 20
7839 10 10 ACCOUNTING
7844 30
7876 20
7900 30
7902 20
7934 10 10 ACCOUNTING
1 Plan hash value: 2962868874
2
3 -----------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 -----------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 14 | 266 | 8 (0)| 00:00:01 |
7 | 1 | NESTED LOOPS OUTER | | 14 | 266 | 8 (0)| 00:00:01 |
8 | 2 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 |
9 | 3 | VIEW | | 1 | 12 | 0 (0)| 00:00:01 |
10 | 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 12 | 1 (0)| 00:00:01 |
11 |* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
12 -----------------------------------------------------------------------------------------
13
14 Predicate Information (identified by operation id):
15 ---------------------------------------------------
16
17 5 - access("B"."DEPTNO"=10)
18 filter("A"."DEPTNO"=10)
可以看到,在访问a表的时候并没有用过滤条件,取出的总的纪录数还是和a表纪录数一致.
其实,当deptno的过滤条件放在join子句中的时候, b.deptno=条件为任何值,结果都回出来14条纪录,
因为此时相当于告诉oracle,我要先对a表全表扫描方式访问a表所有纪录,然后再去和b表做左关联,关联上的话，就把从表对应字段的值填上，关联不上的就置空值。
用oracle传统的方式(+)
用传统(+)也需要主要一些细节，如下
现在我要根据emp，dept表的资料，来查找所有emp信息，如果dept.deptno=10，则把dname取出来，其他的就为null，用外连接操作，以emp表作为主表，
SQL> select empno,a.deptno,b.deptno,b.dname from emp a , dept b where a.deptno=b.deptno(+) and b.deptno=10;--直接对b.deptno做限制
EMPNO DEPTNO DEPTNO DNAME
----- ------ ------ --------------
7782 10 10 ACCOUNTING
7839 10 10 ACCOUNTING
7934 10 10 ACCOUNTING
但是，这个sql结果并不是我们想要的，丢失了deptno<>10的记录，看其执行计划
1 Plan hash value: 568005898
2
3 ----------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 ----------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 3 | 57 | 4 (0)| 00:00:01 |
7 | 1 | NESTED LOOPS | | 3 | 57 | 4 (0)| 00:00:01 |
8 | 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 12 | 1 (0)| 00:00:01 |
9 |* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
10 |* 4 | TABLE ACCESS FULL | EMP | 3 | 21 | 3 (0)| 00:00:01 |
11 ----------------------------------------------------------------------------------------
12
13 Predicate Information (identified by operation id):
14 ---------------------------------------------------
15
16 3 - access("B"."DEPTNO"=10)
17 4 - filter("A"."DEPTNO"=10)
从执行计划来看，oracle自动对a表也加了filter，然后对a，b表做inner join。
如果sql语句改为
SQL> select empno,a.deptno,b.deptno,b.dname from emp a , dept b where a.deptno=b.deptno(+) and b.deptno(+)=10;
EMPNO DEPTNO DEPTNO DNAME
----- ------ ------ --------------
7934 10 10 ACCOUNTING
7839 10 10 ACCOUNTING
7782 10 10 ACCOUNTING
7900 30
7844 30
7698 30
7654 30
7521 30
7499 30
7902 20
7876 20
7788 20
7566 20
7369 20
1 Plan hash value: 657582733
2
3 ----------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 ----------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 14 | 266 | 5 (20)| 00:00:01 |
7 |* 1 | HASH JOIN OUTER | | 14 | 266 | 5 (20)| 00:00:01 |
8 | 2 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 |
9 | 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 12 | 1 (0)| 00:00:01 |
10 |* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
11 ----------------------------------------------------------------------------------------
12
13 Predicate Information (identified by operation id):
14 ---------------------------------------------------
15
16 1 - access("A"."DEPTNO"="B"."DEPTNO"(+))
17 4 - access("B"."DEPTNO"(+)=10)
或者sql语句改为
select empno,a.deptno,b.deptno,b.dname from emp a , (select * from dept where deptno=10) b where a.deptno=b.deptno(+) ;
再来看下面的例子
SQL> select empno,b.deptno,a.deptno,a.dname from emp b , dept a where a.deptno=b.deptno(+) and b.deptno is null;
EMPNO DEPTNO DEPTNO DNAME
----- ------ ------ --------------
99 test
40 OPERATIONS
1 Plan hash value: 2146709594
2
3 ----------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 ----------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 14 | 266 | 7 (15)| 00:00:01 |
7 |* 1 | FILTER | | | | | |
8 |* 2 | HASH JOIN OUTER | | 14 | 266 | 7 (15)| 00:00:01 |
9 | 3 | TABLE ACCESS FULL | DEPT | 5 | 60 | 3 (0)| 00:00:01 |
10 | 4 | TABLE ACCESS FULL | EMP | 14 | 98 | 3 (0)| 00:00:01 |
11 ----------------------------------------------------------------------------
12
13 Predicate Information (identified by operation id):
14 ---------------------------------------------------
15
16 1 - filter("B"."DEPTNO" IS NULL)
17 2 - access("A"."DEPTNO"="B"."DEPTNO"(+))
可以看到，过滤放在了连接操作后，也只有这样，sql出来的结果才是正确的。
在传统的(+)操作中，类似于and a.col1=xxx这样的条件，oracle会按ANSI SQL-92中的where条件过滤情况去处理；如果是类似于and a.col1(+)=xxx,则oracle会先做外连接，然后再在外连接的基础上做过滤，如果不符合过滤条件，只是相应字段为空，如果符合，则置相应的值，记录数在此并不会被过滤掉。
right join
right join的情况和left join类似
1 select empno,a.deptno,b.deptno,b.dname from emp a right join dept b on a.deptno=b.deptno
2* where a.empno=1111
SQL> /
未选定行
1 select empno,a.deptno,b.deptno,b.dname from emp a right join dept b on a.deptno=b.deptno
2* and a.empno=1111
SQL> /
EMPNO DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
30 SALES
99 test
40 OPERATIONS
20 RESEARCH
10 ACCOUNTING
1 select empno,a.deptno,b.deptno,b.dname from emp a , dept b where a.deptno(+)=b.deptno
2* and a.empno=1111
SQL> /
未选定行
SQL> select empno,a.deptno,b.deptno,b.dname from emp a , dept b where a.deptno(+)=b.deptno
2 and a.empno(+)=1111;
EMPNO DEPTNO DEPTNO DNAME
---------- ---------- ---------- --------------
30 SALES
99 test
40 OPERATIONS
20 RESEARCH
10 ACCOUNTING
因此，
如果是ANSI SQL-92标准的外连接语句
(4) 当在内连接查询中加入条件时，无论是将它加入到join子句，还是加入到where子句，其效果是完全一样的，但对于外连接情况就不同了。
(5) 当把条件加入到join子句时，oracle首先对相关表进行连接,然后再对连接结果做过滤操作,符合条件的纪录会返回完整的记录结果,不符合过滤条件的,相应的字段为空,总之,总的纪录数肯定是和主表纪录数一致。
(6) 如果将条件放到where子句中，oracle优化器可能会先对相应表应用where子句进行筛选,然后进行连接操作，也可能会做完表与表的连接再来对结果集合做筛选，在这种情况下，和放在join中的and区别是: where语句会过滤掉不符合条件的记录，总的记录数要比主表的记录数少。
如果是oracle传统的(+)外连接语句
(3) 在传统的(+)操作中，如果有类似于and a.col1=xxx这样的限制条件，其功能和处理方式，和ANSI SQL-92标准中限制条件放在where子句的情况相同。
(4) 如果是类似于and a.col1(+)=xxx,则oracle会先做外连接，然后再在外连接的基础上做过滤，如果不符合过滤条件，只是相应字段为空，如果符合，则置相应的值，记录数在此并不会被过滤掉。相对应于ANSI SQL-92标准中限制条件放在join子句的情况。
【http://sns.linuxpk.com/blog-52196-16766.html】
扫一扫
961
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
