Oracle外连接中对非连接条件使用(+)的作用

1.先来说下Oracle外连接语句中对非链接条件使用(+)的作用问题

之前问过朋友,当时大脑处于短路状态,居然没想明白作用是啥。先看例子如下:

select * from dept,emp where dept.deptno=emp.deptno(+) and emp.ename(+)!='KING';
使用scott账户登录,执行上面的sql语句,可以分析出此sql语句的意图是将部门表和员工表进行左外链,找出链接中员工名字不为‘KING’的记录,在emp.ename后面加上(+)后,名字为空的记录也会列出来,即没有员工的部门也会列出来,如果不加(+),这样的记录就列不出来。

2.上面是使用oracle自己的外联结语法的sql语句,如果使用ANSIsql1992标准,即left join,那么情况会有所变化,(+)不能同时和ANSI标准的join一起使用,那么我想emp.ename后面的(+)应该变成 emp.ename is null,(可经过试验,发现根据ename字段的类型不同,结果有所不同,一下列出几个sql语句,供试验,待有执行环境后,整理之,本次只整理了varchar的情况)

--vacrchar类型
    select * from dept,emp where dept.deptno=emp.deptno(+) and emp.ename!='KING';
    select * from dept left join emp on(dept.deptno=emp.deptno) where emp.ename!='KING';

select * from dept,emp where dept.deptno=emp.deptno(+) and emp.ename(+)!='KING';
    select * from dept left join emp on(dept.deptno=emp.deptno) where emp.ename!='KING' or emp.ename is null;

--number类型
    select * from dept,emp where dept.deptno=emp.deptno(+) and emp.empno!=7782;
    select * from dept left join emp on(dept.deptno=emp.deptno) and emp.empno!=7782;

select * from dept,emp where dept.deptno=emp.deptno(+) and emp.empno(+)!=7782;
    select * from dept left join emp on(dept.deptno=emp.deptno) and emp.empno!=7782 or emp.empno is null;
--char类型又不一样

(以上用!='KING',用=‘KING’又将如何?)

----------------------------------------------------------------------------------------------

分析:

先列出两张表的数据
dept:
DEPTNODNAMELOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
emp:
DEPTNOEMPNOENAMEJOBMGRHIREDATESALCOMM
10 7782 CLARK MANAGER 7839 1981-6-9 2450.00  
10 7839 KING PRESIDENT   1981-11-17 5000.00  
10 7934 MILLER CLERK 7782 1982-1-23 1300.00  
20 7369 SMITH CLERK 7902 1980-12-17 800.00  
20 7566 JONES MANAGER 7839 1981-4-2 2975.00  
20 7788 SCOTT ANALYST 7566 1987-4-19 3000.00  
20 7876 ADAMS CLERK 7788 1987-5-23 1100.00  
20 7902 FORD ANALYST 7566 1981-12-3 3000.00  
30 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00
30 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00
30 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00
30 7698 BLAKE MANAGER 7839 1981-5-1 2850.00  
30 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00
30 7900 JAMES CLERK 7698 1981-12-3 950.00  
dept对emp做外连接后的结果是:
DEPTNODNAMELOCEMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
10 ACCOUNTING NEW YORK 7782 CLARK MANAGER 7839 1981-6-9 2450.00   10
10 ACCOUNTING NEW YORK 7839 KING PRESIDENT   1981-11-17 5000.00   10
10 ACCOUNTING NEW YORK 7934 MILLER CLERK 7782 1982-1-23 1300.00   10
20 RESEARCH DALLAS 7369 SMITH CLERK 7902 1980-12-17 800.00   20
20 RESEARCH DALLAS 7566 JONES MANAGER 7839 1981-4-2 2975.00   20
20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 1987-4-19 3000.00   20
20 RESEARCH DALLAS 7876 ADAMS CLERK 7788 1987-5-23 1100.00   20
20 RESEARCH DALLAS 7902 FORD ANALYST 7566 1981-12-3 3000.00   20
30 SALES CHICAGO 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
30 SALES CHICAGO 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
30 SALES CHICAGO 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
30 SALES CHICAGO 7698 BLAKE MANAGER 7839 1981-5-1 2850.00   30
30 SALES CHICAGO 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
30 SALES CHICAGO 7900 JAMES CLERK 7698 1981-12-3 950.00   30
40 OPERATIONS BOSTON                
最后一行,编号为40的部门是外连接的效果,emp表对应的字段都为空
  • 首先,对于!=号,varchar
    现在考虑如下情况,如果想要找出所有部门中员工姓名不为King的员工和部门都找出来,并且没有员工的部门也列出来,那么一般会想到用
select * from dept,emp where dept.deptno=emp.deptno(+) and emp.ename!='KING';
这条sql语句,但是发现40这个部门并不在结果中,也就是说,最后一行中虽然emp的ename为空,符合!=‘KING’的条件,但却没有作为结果返回,似乎oracle认为这条记录不存在,于是想要这条结果出来,那么必须在条件emp.ename!='KING'处加上一个(+)即
select * from dept,emp where dept.deptno=emp.deptno(+) and emp.ename(+)!='KING',使得好像是外联结果产生的记录中对应于emp表的ename字段的值是任何一个不为‘KING’的字符串,这样这条记录便被算作是结果列了出来。
    同样,使用ANSI语法的语句
select * from dept left join emp on(dept.deptno=emp.deptno) where emp.ename!='KING'
也没有达到效果,仍然将外联结产生的记录排除在外了,如果想要包含该条记录,就应该加上emp.ename is null,即
select * from dept left join emp on(dept.deptno=emp.deptno) where emp.ename!='KING' or emp.ename is null
  • 其次,再来看=号的情况
    再来看这样一个需求,如果我们想看一下每个部门及其助理的信息,并且如果该部门没有助理的话,把部门信息列出来,助理的信息显示空,那么我们可能会想到如下sql
select * from dept,emp where dept.deptno=emp.deptno(+) and emp.job='ASSISTENT'
但是结果是没有任何记录,因为外联结结果中没有任何记录符合其员工职位为ASSITENT,如果要达到我们的要求,sql语句应该写为
select * from dept,emp where dept.deptno=emp.deptno(+) and emp.job(+)='ASSISTENT' ,
(+)的作用就好象是使oracle造出了这样的外联结记录,部门的员工中有一个的工作职位是ASSISTENT,但因为实际并没有这样的记录,所以这条记录的emp表的字段都是空。
    我们可能希望ANSI格式的SQL语句也能达到这样的效果,你也许可能想到加上emp.job is null不久可以了么,但是那样只会列出外连接记录,即部门40的记录,部门10,20和30的记录都不会列出来.
所以需要做如下变通,通过左外连接一个子查询来实现:
select * from dept left join (select * from emp where ename='ASSISTENT') t on dept.deptno=t.deptno

  • 注意:
     如上的分析是dept对emp进行左外联,外联条件也是dept.deptno=emp.deptno(+),我们注意到(+)是加在右边的表上,而条件中我们的(+)也是加在右边表的字段上,而不是常量值上,如emp.job(+)='ASSISTENT' ,上面两种情况都是对于条件是在右边的表的情况,如果条件是在左边的表中呢?加上(+)又是什么效果?
 
 因为左连接在没有其他任何条件的情况下,会将左边表中的所有记录都列出来,实验发现,当条件中只有关于左边表中的条件时,无论!=还是=的情况,不论加上(+)还是不加,效果都是一样的,都不会有多余记录列出。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值