ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 外连接的使用

oracle 外连接的使用

原创 Linux操作系统 作者:oracle_kai 时间:2009-04-03 19:06:52 0 删除 编辑

外连接分为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是先把ab表的记录都取出来,然后做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 传统的方式(+)

用传统(+) 也需要主要一些细节,如下

现在我要根据empdept表的资料,来查找所有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,然后对ab表做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子句的情况。

 

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

上一篇: Oracle树形查找
请登录后发表评论 登录
全部评论

注册时间:2007-12-20

  • 博文量
    48
  • 访问量
    175826