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:
10 | ACCOUNTING | NEW YORK |
20 | RESEARCH | DALLAS |
30 | SALES | CHICAGO |
40 | OPERATIONS | BOSTON |
emp:
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做外连接后的结果是:
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
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
- 其次,再来看=号的情况
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
- 注意:
因为左连接在没有其他任何条件的情况下,会将左边表中的所有记录都列出来,实验发现,当条件中只有关于左边表中的条件时,无论!=还是=的情况,不论加上(+)还是不加,效果都是一样的,都不会有多余记录列出。
相关推荐
其一、就业面广:全球前100强企业99家都在使用ORACLE相关技术,中国政府机构,大中型企事业单位都能有ORACLE技术的工程师岗位。 其二、技术层次深:如果期望进入IT服务或者产品公司(类似毕博、DELL、IBM等),...
在Oracle应用程序的开发过程中,访问数据库对象和编写SQL程序是一件乏味且耗费时间的工作,对数据库进行日常管理也是需要很多SQL脚本才能完成的。Quest Software为此提供了高效的Oracle应用开发工具-Toad(Tools of ...
ORDER BY子句中使用的列将是为显示所选择的列,但是实际上并不一定要这样,用非检索的列排序数据是完全合法的。 为了按多个列排序,列名之间用逗号分开。 2、支持按相对列位置进行排序。 输入 SELECT prod_id,...
2、如果方法一操作后没有变化,此时需要去查看服务器操作系统中是否对连接数做了限制,AIX下可以直接vi文件limits修改其中的连接限制数、端口数,还有tcp连接等待时间间隔大小,wiodows类似,只不过windows修改...
比如在不需要物化视图的地方使用的物化视图表,那么会对源表的增删改性能造成影响。 数据库设计准则及方法论全文共5页,当前为第2页。数据库设计准则及方法论全文共5页,当前为第2页。 数据库设计准则及方法论全文共...
但是读完这本书,你对书中这些蕴含的思想也许需要一种更明晰更系统更透彻的了解和掌握,那么你就需要研读 GoF 的《设 计模式》了。 《Thingking in Java》(第一版中文)是这样描述设计模式的:他在由 Gamma, Helm 和...
子类的对象使用这个方法时,将调用子类中的定义,对它而言,父类中的定义如同被"屏蔽"了。如果在一个类中定义了多个同名的方法,它们或有不同的参数个数或有不同的参数类型,则称为方法的重载(Overloading)。...
子类的对象使用这个方法时,将调用子类中的定义,对它而言,父类中的定义如同被"屏蔽"了。如果在一个类中定义了多个同名的方法,它们或有不同的参数个数或有不同的参数类型,则称为方法的重载(Overloading)。...
但是还需要修改以前Project的数据库连接属性 否则会提示错误 然后还要修改 C:\Program Files\Mercury\Quality Center\repository\qc中的dbcon.txt: qcsiteadmin_db@192.168.1.12.1433. 还有 C:\Program Files\...
今天一个客户给我们打来电话说他们使用的软件不能连接上服务器了,这款软件昨天还是可以正常使用的。我们的第一反应是不是客户端软件的缺陷导致软件故障。询问了有关软件出现故障时的错误提示信息后初步判断是由于...
查询出列表,也就是返回list, 在我们这个例子中也就是 List<User> , 这种方式返回数据,需要在User.xml 里面配置返回的类型 resultMap, 注意不是 resultType, 而这个resultMap 所对应的应该是我们自己配置的 ...