oracle 外链接 加条件,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：

DEPTNO

DNAME

LOC

10

ACCOUNTING

NEW YORK

20

RESEARCH

DALLAS

30

SALES

CHICAGO

40

OPERATIONS

BOSTON

emp:

DEPTNO

EMPNO

ENAME

JOB

MGR

HIREDATE

SAL

COMM

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做外连接后的结果是：

DEPTNO

DNAME

LOC

EMPNO

ENAME

JOB

MGR

HIREDATE

SAL

COMM

DEPTNO

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'

，上面两种情况都是对于条件是在右边的表的情况，如果条件是在左边的表中呢？加上(+)又是什么效果？

因为左连接在没有其他任何条件的情况下，会将左边表中的所有记录都列出来，实验发现，当条件中只有关于左边表中的条件时，无论！=还是=的情况，不论加上(+)还是不加，效果都是一样的，都不会有多余记录列出。