oracle 外连接的使用

外连接分为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】

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值