当前位置:首页 > 开发 > 数据库 > 正文

oracle外连接符号(+)的用法

发表于: 2015-07-13   作者:yhgogo   来源:转载   浏览:
摘要: 我们都知道,PL/SQL中实现外连接,除了可以用关键词OUTER JOIN外,还可以用Oracle的外连接符号(+)。对于这个外连接符号(+),虽然看到书上说:使用(+)进行外连接时,where条件中,对于附表的字段都应带上(+)。但在实际应用中,发现对它的理解还远远不够。现在结合实际的SQL语句,让我们再仔细地瞧瞧这个(+)的"真面目"吧。 首先,我们来看一下Oralce的

我们都知道,PL/SQL中实现外连接,除了可以用关键词OUTER JOIN外,还可以用Oracle的外连接符号(+)。对于这个外连接符号(+),虽然看到书上说:使用(+)进行外连接时,where条件中,对于附表的字段都应带上(+)。但在实际应用中,发现对它的理解还远远不够。现在结合实际的SQL语句,让我们再仔细地瞧瞧这个(+)的"真面目"吧。

首先,我们来看一下Oralce的《SQL Reference》对它的一点说明。

—————————————————————————————————————

If the WHERE clause contains a condition that compares a column from table B with a
constant, then the (+) operator must be applied to the column so that Oracle returns
the rows from table A for which it has generated nulls for this column. Otherwise
Oracle returns only the results of a simple join.

                                                                    注:table A为主表,table B为附表。

—————————————————————————————————————

现在有以下两张表:

                                              表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没带外连接符号。还有种写法,如语句三: 

    语句三:

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

       该语句首先将表A和表B通过外连接关联起来,然后对表B中的字段DEL_FlG进行限定,因为要关    联表B中的有效字段,所以有条件DEL_FLG = '0',又因为是左连接,所以会有结果集中表B的记录为空的情况,所以又加上条件DEL_FLG IS NULL
 

      粗粗看起来,以上三种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


                                                语句三的执行结果

  

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

 

 

 

 

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

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

    那么语句二和三是怎么回事呢?在语句二中,虽然对两张表中的相关字段进行了外连接,但是,对附表B中的字段DEL_FLG限定条件的时候,没有使用外连接符号,导致这个连接变成了全连接。语句三的话,最具有迷惑性,表面看上去很有道理,但实际上它不是真正的外连接,

    它把跟附表B中的无效记录相关联的表A中的记录也排除在外了,这是不符号外连接要求的。最重要的是,我们要明白对于外连接,有主表和附表之分,主表中的记录要全部选出,而附表中的记录可有可无。

 

 

 转自:http://blog.csdn.net/feifei666888/article/details/5653601

 

oracle外连接符号(+)的用法

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
在上篇文章关于内连接的讨论中,我们使用自连接查找出所有人名以及他的上司的姓名,但是有一个问题
==================== 内连接 ==================== 内链接实际上就是利用where子句对两张表形成的
2、问题:在介绍完知识后,开始实践 已知:SJ_GD_TEACHERS表(毕业设计指导教师表)、SJ_GD_REPLY表
elect ename,job,sal from emp where deptno>10 order by sal desc;
工作2年多了,虽然天天在用Oracle,但基本比较简单的CRUD,今天复习一下 连接方式: Oracle 表之间的
1.简述 1) 两个表的连接,是通过将一个表中的一列或者多列同另一个表中的列链接而建立起来的。用来
1.简述 1) 两个表的连接,是通过将一个表中的一列或者多列同另一个表中的列链接而建立起来的。用来
  从两个或更多个表中组合数据的过程称为连接表。数据库管理程序从指定的表中形成行的所有组合。对
Employees表: Department表: select e.last_name,e.department_id,d.department_name <p class="
Oracle 表之间的连接分为三种: 1. 内连接(自然连接) 2. 外连接 (1)左外连接 (左边的表不加限制)
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号