Oracle SQL篇（五） oracle等值连接与外连接操作_zhutianjie

http://blog.sina.com.cn/u/1776793815

首页博文目录关于我

个人资料

微博

加好友发纸条

写留言加关注

博客等级：
博客积分：

博客访问：
关注人气：
获赠金笔：0支
赠出金笔：0支
荣誉徽章：

正文字体大小：大中小

Oracle SQL篇（五） oracle等值连接与外连接操作

(2012-03-05 09:31:55)

标签：

oracle

join

等值连接

外连接

sql99语法

it

分类： oracle技术

ORACLE数据库的等值连接与外连接

为了能够让够让更多的非Oracle数据库使用者非常快速的使用SQL语句操作数据库，Oracle公司做出了妥协，从9i开始兼容SQL99的语法标准，也就是从这个时候开始，oracle有两种不同的SQL语法标准共同出现。当然了，象我们所熟悉的MERGE操作，全外连接操作，CASE表达式等，也填补和丰富了Oracle的语法，使得Oracle语法功能得到了进一步的完善。

下表就是Oracle的语法和SQL99的语法：

http://s6/middle/69e7b8d7t790bb1e8e485&690SQL篇（五） oracle等值连接与外连接操作" TITLE="Oracle SQL篇（五） oracle等值连接与外连接操作" />

在这里我们来介绍一下等值连接和外连接操作，这也是Oracle数据库当中使用最广泛的两种连接。绝大多数的RDBMS数据库在设计的时候，会更多的考虑满足3NF范式的要求，这就导致了一些相关的数据存到了不同的表里，比如说部门表dept和员工表emp：

scott@DB01> select * from dept;

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

scott@DB01> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

当然了，为了将来能够方便操作，我们通常会在表上建立主外键约束关系（不是必须的），并通过主外键的对应关系来找到我们想要的数据，在上面的两张表中deptno是dept表的主键，而deptno是emp表的外键。这其实就是Oracle的等值连接（或者称为内连接）想要达到的目的。比如我们有这样一个需求，找到员工表中的员工编号和员工名称以及他所在的部门编号、部门名称和部门所在地，我们来看一下实现的语句：

scott@DB01> select dept.deptno,dname,loc,empno,ename

2 from emp,dept

3 where emp.deptno=dept.deptno;

DEPTNO DNAME LOC EMPNO ENAME

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

20 RESEARCH DALLAS 7369 SMITH

30 SALES CHICAGO 7499 ALLEN

30 SALES CHICAGO 7521 WARD

20 RESEARCH DALLAS 7566 JONES

30 SALES CHICAGO 7654 MARTIN

30 SALES CHICAGO 7698 BLAKE

10 ACCOUNTING NEW YORK 7782 CLARK

20 RESEARCH DALLAS 7788 SCOTT

10 ACCOUNTING NEW YORK 7839 KING

30 SALES CHICAGO 7844 TURNER

20 RESEARCH DALLAS 7876 ADAMS

30 SALES CHICAGO 7900 JAMES

20 RESEARCH DALLAS 7902 FORD

10 ACCOUNTING NEW YORK 7934 MILLER

这就是Oracle的等值连接，不过对于这样的一个语句来说，有三个地方最好做一些调整：

1.不同表的列使用表的名称作为前缀（如果表使用了别名，前缀就不能再用表的真实名字）

2.不使用表的原名称而使用表的别名，这样语句会更简短（有用吗？当然，oracle会把语句的完整文本放到内存中，语句越短，占内存越小）

3.格式化语句，考虑折行、语句区分大小写（如果你的语句有问题，没人愿意为你的乱七八糟的语句给出建议）

以下是我的书写习惯，仅供参考

select d.deptno,

d.dname,

d.loc,

e.empno,

e.ename

from emp e,

dept d

where e.deptno=d.deptno;

DEPTNO DNAME LOC EMPNO ENAME

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

20 RESEARCH DALLAS 7369 SMITH

30 SALES CHICAGO 7499 ALLEN

30 SALES CHICAGO 7521 WARD

20 RESEARCH DALLAS 7566 JONES

30 SALES CHICAGO 7654 MARTIN

30 SALES CHICAGO 7698 BLAKE

10 ACCOUNTING NEW YORK 7782 CLARK

20 RESEARCH DALLAS 7788 SCOTT

10 ACCOUNTING NEW YORK 7839 KING

30 SALES CHICAGO 7844 TURNER

20 RESEARCH DALLAS 7876 ADAMS

30 SALES CHICAGO 7900 JAMES

20 RESEARCH DALLAS 7902 FORD

10 ACCOUNTING NEW YORK 7934 MILLER

以上的连接方法是Oracle自己的语法，当然对于这样的需求使用SQL99语法也非常的容易，并且可以使用其中的几种语法来实现，在这里我们仅以自然连接（natural join）来举一个小

例子：

scott@DB01> select deptno,

2 d.dname,

3 d.loc,

4 e.empno,

5 e.ename

6 from emp e natural join dept d;

DEPTNO DNAME LOC EMPNO ENAME

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

20 RESEARCH DALLAS 7369 SMITH

30 SALES CHICAGO 7499 ALLEN

30 SALES CHICAGO 7521 WARD

20 RESEARCH DALLAS 7566 JONES

30 SALES CHICAGO 7654 MARTIN

30 SALES CHICAGO 7698 BLAKE

10 ACCOUNTING NEW YORK 7782 CLARK

20 RESEARCH DALLAS 7788 SCOTT

10 ACCOUNTING NEW YORK 7839 KING

30 SALES CHICAGO 7844 TURNER

20 RESEARCH DALLAS 7876 ADAMS

30 SALES CHICAGO 7900 JAMES

20 RESEARCH DALLAS 7902 FORD

10 ACCOUNTING NEW YORK 7934 MILLER

当然对于自然连接，Oracle有一些限制，比如要求两个表的列的名称和数据类型一样，另外，在做查询的时候，连接的列不能使用前缀，否则会报错

ORA-25155: column used in NATURAL join cannot have qualifier，如果列的类型不一样的话，我们可以选择使用join using语法。等值连接是oracle数据库当中最简单也是使用最多的一种关联方式。

我们再来看一下Oracle的外连接操作，凭心而论，Oracle的语法实现比SQL99外连接语法要简单一些，不过没有SQL99语法具体和完善，比如Oracle没有全外连接的概念。

还是先来看一下为什么要使用外连接吧！

如果我们有这样的一个需求，列出公司里所有的部门和部门下的员工，很显然，用上面的等值连接是实现不了的。

scott@DB01> select * from dept;

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

我们看到40部门是存在的，但是等值连接里并不能显示这样的记录，道理很简单，40部门没有一个员工，所以在和员工表做等值连接的时候无法显示出来，那如何来实现这样的需求呢？很简单 Oracle提供了（+）这样的外连接方法

我们来看下面的句子:

scott@DB01> select d.deptno,

2 d.dname,

3 d.loc,

4 e.empno,

5 e.ename

6 from emp e,

7 dept d

8 where d.deptno=e.deptno(+);

DEPTNO DNAME LOC EMPNO ENAME

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

10 ACCOUNTING NEW YORK 7782 CLARK

10 ACCOUNTING NEW YORK 7839 KING

10 ACCOUNTING NEW YORK 7934 MILLER

20 RESEARCH DALLAS 7566 JONES

20 RESEARCH DALLAS 7902 FORD

20 RESEARCH DALLAS 7876 ADAMS

20 RESEARCH DALLAS 7369 SMITH

20 RESEARCH DALLAS 7788 SCOTT

30 SALES CHICAGO 7521 WARD

30 SALES CHICAGO 7844 TURNER

30 SALES CHICAGO 7499 ALLEN

30 SALES CHICAGO 7900 JAMES

30 SALES CHICAGO 7698 BLAKE

30 SALES CHICAGO 7654 MARTIN

40 OPERATIONS BOSTON

我们看到40部门（OPERATIONS）被显示出来了，当然由于他没有员工所以在员工信息部分会补空值。也许有人会有这样的疑问，where d.deptno=e.deptno(+)中的（+）符号是应该出现在那个表的列后面呢？是随意的吗？

我们来稍微总结一下：

1.（+）只能在关联条件的一端出现，本例中是e.deptno(+)，也可以说是e表的一边

2.如果在where后面有其他的条件，那其他的所有条件都要使用（+）

3.(+)出现在值少的一端，比如本例中，员工表没有员工能和40部门对应，我们认为员工表记录少

我们来看部门表记录少的一个例子：

scott@DB01> update emp set deptno=null where empno=7369;

1 row updated.

scott@DB01> commit;

Commit complete.

scott@DB01> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 17-DEC-80 800

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

通过修改，我们把7369（SMITH）的部门号置为空，这就意味着，当我们使用部门号deptno做等值查询时，7369不会显示出来，因为部门表没有一条记录可以和7369对应，现在的情况是员工表记录多，如果我们想要把7369显示，就要在少的一端（部门表列）上添加(+)

scott@DB01> select d.deptno,

2 d.dname,

3 d.loc,

4 e.empno,

5 e.ename

6 from emp e,

7 dept d

8 where d.deptno(+)=e.deptno;

DEPTNO DNAME LOC EMPNO ENAME

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

7369 SMITH

30 SALES CHICAGO 7499 ALLEN

30 SALES CHICAGO 7521 WARD

20 RESEARCH DALLAS 7566 JONES

30 SALES CHICAGO 7654 MARTIN

30 SALES CHICAGO 7698 BLAKE

10 ACCOUNTING NEW YORK 7782 CLARK

20 RESEARCH DALLAS 7788 SCOTT

10 ACCOUNTING NEW YORK 7839 KING

30 SALES CHICAGO 7844 TURNER

20 RESEARCH DALLAS 7876 ADAMS

30 SALES CHICAGO 7900 JAMES

20 RESEARCH DALLAS 7902 FORD

10 ACCOUNTING NEW YORK 7934 MILLER

最后我们来看这样的需求，假如我们针对现有的数据，把所有的部门和所有的员工都要显示出来，那如何实现呢，我们知道现在两张表都有多出的数据。dept表里是40部门，员工表里是7369员工，对于Oracle的语法来说，是不能在关联列两端都使用(+)的，那实现起来就会很困难。但是这样的需求，对于使用SQL99的语法来说，就是非常容易的。

先来简单的介绍一下SQL99的语法：

SELECT table1.column, table2.column

FROM table1

[LEFT|RIGHT|FULL OUTER JOIN table2

ON (table1.column_name = table2.column_name)];

我们注意到SQL99的语法外连接有3种：a.左外连接b.右外连接c.全外连接

针对前面的需求，我们分别来举一个例子

1.把多的部门40显示出来使用右外连接right outer join

scott@DB01> select d.deptno,

2 d.dname,

3 d.loc,

4 e.empno,

5 e.ename

6 from emp e

7 right outer join dept d

8 on(d.deptno=e.deptno);

DEPTNO DNAME LOC EMPNO ENAME

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

10 ACCOUNTING NEW YORK 7782 CLARK

10 ACCOUNTING NEW YORK 7839 KING

10 ACCOUNTING NEW YORK 7934 MILLER

20 RESEARCH DALLAS 7566 JONES

20 RESEARCH DALLAS 7902 FORD

20 RESEARCH DALLAS 7876 ADAMS

20 RESEARCH DALLAS 7788 SCOTT

30 SALES CHICAGO 7521 WARD

30 SALES CHICAGO 7844 TURNER

30 SALES CHICAGO 7499 ALLEN

30 SALES CHICAGO 7900 JAMES

30 SALES CHICAGO 7698 BLAKE

30 SALES CHICAGO 7654 MARTIN

40 OPERATIONS BOSTON

2.把多出来的员工7369显示出来使用左外连接left outer join

scott@DB01> select d.deptno,

2 d.dname,

3 d.loc,

4 e.empno,

5 e.ename

6 from emp e

7 left outer join dept d

8 on(d.deptno=e.deptno);

DEPTNO DNAME LOC EMPNO ENAME

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

7369 SMITH

30 SALES CHICAGO 7499 ALLEN

30 SALES CHICAGO 7521 WARD

20 RESEARCH DALLAS 7566 JONES

30 SALES CHICAGO 7654 MARTIN

30 SALES CHICAGO 7698 BLAKE

10 ACCOUNTING NEW YORK 7782 CLARK

20 RESEARCH DALLAS 7788 SCOTT

10 ACCOUNTING NEW YORK 7839 KING

30 SALES CHICAGO 7844 TURNER

20 RESEARCH DALLAS 7876 ADAMS

30 SALES CHICAGO 7900 JAMES

20 RESEARCH DALLAS 7902 FORD

10 ACCOUNTING NEW YORK 7934 MILLER

3.即显示多余的部门40，也显示多出来的员工7369 使用全外连接full outer join

scott@DB01> select d.deptno,

2 d.dname,

3 d.loc,

4 e.empno,

5 e.ename

6 from emp e

7 full outer join dept d

8 on(d.deptno=e.deptno);

DEPTNO DNAME LOC EMPNO ENAME

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

7369 SMITH

30 SALES CHICAGO 7499 ALLEN

30 SALES CHICAGO 7521 WARD

20 RESEARCH DALLAS 7566 JONES

30 SALES CHICAGO 7654 MARTIN

30 SALES CHICAGO 7698 BLAKE

10 ACCOUNTING NEW YORK 7782 CLARK

20 RESEARCH DALLAS 7788 SCOTT

10 ACCOUNTING NEW YORK 7839 KING

30 SALES CHICAGO 7844 TURNER

20 RESEARCH DALLAS 7876 ADAMS

30 SALES CHICAGO 7900 JAMES

20 RESEARCH DALLAS 7902 FORD

10 ACCOUNTING NEW YORK 7934 MILLER

40 OPERATIONS BOSTON

具体应该使用哪一种连接，自己体会一下数据的分布以及语句的需求是非常容易理解的。

当然了，不管是Oracle的语法还是SQL99的语法，从效率上来讲是一样的，采用哪一个，完全取决于查询人员的习惯。

阅读┊ 收藏 ┊ 喜欢 ▼ ┊打印┊举报/Report

前一篇：oracle数据库的测试用户 SCOTT

后一篇：随意写写，我也想看看标准答案

新浪BLOG意见反馈留言板　欢迎批评指正