oracle等值连接与外连接操作.docx
- 文档编号:8478983
- 上传时间:2023-01-31
- 格式:DOCX
- 页数:12
- 大小:69.96KB
oracle等值连接与外连接操作.docx
《oracle等值连接与外连接操作.docx》由会员分享,可在线阅读,更多相关《oracle等值连接与外连接操作.docx(12页珍藏版)》请在冰豆网上搜索。
oracle等值连接与外连接操作
Oracle SQL篇(五) oracle等值连接与外连接操作
ORACLE数据库的等值连接与外连接
为了能够让够让更多的非Oracle数据库使用者非常快速的使用SQL语句操作数据库,Oracle公司做出了妥协,从9i开始兼容SQL99的语法标准,也就是从这个时候开始,oracle有两种不同的SQL语法标准共同出现。
当然了,象我们所熟悉的MERGE操作,全外连接操作,CASE表达式等,也填补和丰富了Oracle的语法,使得Oracle语法功能得到了进一步的完善。
下表就是Oracle的语法和SQL99的语法:
在这里我们来介绍一下等值连接和外连接操作,这也是Oracle数据库当中使用最广泛的两种连接。
绝大多数的RDBMS数据库在设计的时候,会更多的考虑满足3NF范式的要求,这就导致了一些相关的数据存到了不同的表里,比如说部门表dept和员工表emp:
scott@DB01>select*fromdept;
DEPTNODNAME LOC
-------------------------------------
10ACCOUNTING NEWYORK
20RESEARCH DALLAS
30SALES CHICAGO
40OPERATIONS BOSTON
scott@DB01>select*fromemp;
EMPNOENAME JOB MGRHIREDATE SAL COMM DEPTNO
---------------------------------------------------------------------------------
7369SMITH CLERK 790217-DEC-80 800 20
7499ALLEN SALESMAN 769820-FEB-81 1600 300 30
7521WARD SALESMAN 769822-FEB-81 1250 500 30
7566JONES MANAGER 783902-APR-81 2975 20
7654MARTIN SALESMAN 769828-SEP-81 1250 1400 30
7698BLAKE MANAGER 783901-MAY-81 2850 30
7782CLARK MANAGER 783909-JUN-81 2450 10
7788SCOTT ANALYST 756619-APR-87 3000 20
7839KING PRESIDENT 17-NOV-81 5000 10
7844TURNER SALESMAN 769808-SEP-81 1500 0 30
7876ADAMS CLERK 778823-MAY-87 1100 20
7900JAMES CLERK 769803-DEC-81 950 30
7902FORD ANALYST 756603-DEC-81 3000 20
7934MILLER CLERK 778223-JAN-82 1300 10
当然了,为了将来能够方便操作,我们通常会在表上建立主外键约束关系(不是必须的),并通过主外键的对应关系来找到我们想要的数据,在上面的两张表中deptno是dept表的主键,而deptno是emp表的外键。
这其实就是Oracle的等值连接(或者称为内连接)想要达到的目的。
比如我们有这样一个需求,找到员工表中的员工编号和员工名称以及他所在的部门编号、部门名称和部门所在地,我们来看一下实现的语句:
scott@DB01>selectdept.deptno,dname,loc,empno,ename
2 fromemp,dept
3 whereemp.deptno=dept.deptno;
DEPTNODNAME LOC EMPNOENAME
---------------------------------------------------------
20RESEARCH DALLAS 7369SMITH
30SALES CHICAGO 7499ALLEN
30SALES CHICAGO 7521WARD
20RESEARCH DALLAS 7566JONES
30SALES CHICAGO 7654MARTIN
30SALES CHICAGO 7698BLAKE
10ACCOUNTING NEWYORK 7782CLARK
20RESEARCH DALLAS 7788SCOTT
10ACCOUNTING NEWYORK 7839KING
30SALES CHICAGO 7844TURNER
20RESEARCH DALLAS 7876ADAMS
30SALES CHICAGO 7900JAMES
20RESEARCH DALLAS 7902FORD
10ACCOUNTING NEWYORK 7934MILLER
这就是Oracle的等值连接,不过对于这样的一个语句来说,有三个地方最好做一些调整:
1.不同表的列使用表的名称作为前缀(如果表使用了别名,前缀就不能再用表的真实名字)
2.不使用表的原名称而使用表的别名,这样语句会更简短(有用吗?
当然,oracle会把语句的完整文本放到内存中,语句越短,占内存越小)
3.格式化语句,考虑折行、语句区分大小写(如果你的语句有问题,没人愿意为你的乱七八糟的语句给出建议)
以下是我的书写习惯,仅供参考
selectd.deptno,
d.dname,
d.loc,
e.empno,
e.ename
fromemp e,
deptd
wheree.deptno=d.deptno;
DEPTNODNAME LOC EMPNOENAME
---------------------------------------------------------
20RESEARCH DALLAS 7369SMITH
30SALES CHICAGO 7499ALLEN
30SALES CHICAGO 7521WARD
20RESEARCH DALLAS 7566JONES
30SALES CHICAGO 7654MARTIN
30SALES CHICAGO 7698BLAKE
10ACCOUNTING NEWYORK 7782CLARK
20RESEARCH DALLAS 7788SCOTT
10ACCOUNTING NEWYORK 7839KING
30SALES CHICAGO 7844TURNER
20RESEARCH DALLAS 7876ADAMS
30SALES CHICAGO 7900JAMES
20RESEARCH DALLAS 7902FORD
10ACCOUNTING NEWYORK 7934MILLER
以上的连接方法是Oracle自己的语法,当然对于这样的需求使用SQL99语法也非常的容易,并且可以使用其中的几种语法来实现,在这里我们仅以自然连接(naturaljoin)来举一个小
例子:
scott@DB01>selectdeptno,
2 d.dname,
3 d.loc,
4 e.empno,
5 e.ename
6 fromemp enaturaljoindeptd;
DEPTNODNAME LOC EMPNOENAME
---------------------------------------------------------
20RESEARCH DALLAS 7369SMITH
30SALES CHICAGO 7499ALLEN
30SALES CHICAGO 7521WARD
20RESEARCH DALLAS 7566JONES
30SALES CHICAGO 7654MARTIN
30SALES CHICAGO 7698BLAKE
10ACCOUNTING NEWYORK 7782CLARK
20RESEARCH DALLAS 7788SCOTT
10ACCOUNTING NEWYORK 7839KING
30SALES CHICAGO 7844TURNER
20RESEARCH DALLAS 7876ADAMS
30SALES CHICAGO 7900JAMES
20RESEARCH DALLAS 7902FORD
10ACCOUNTING NEWYORK 7934MILLER
当然对于自然连接,Oracle有一些限制,比如要求两个表的列的名称和数据类型一样,另外,在做查询的时候,连接的列不能使用前缀,否则会报错
ORA-25155:
columnusedinNATURALjoincannothavequalifier,如果列的类型不一样的话,我们可以选择使用joinusing语法。
等值连接是oracle数据库当中最简单也是使用最多的一种关联方式。
我们再来看一下Oracle的外连接操作,凭心而论,Oracle的语法实现比SQL99外连接语法要简单一些,不过没有SQL99语法具体和完善,比如Oracle没有全外连接的概念。
还是先来看一下为什么要使用外连接吧!
如果我们有这样的一个需求,列出公司里所有的部门和部门下的员工,很显然,用上面的等值连接是实现不了的。
scott@DB01>select*fromdept;
DEPTNODNAME LOC
-------------------------------------
10ACCOUNTING NEWYORK
20RESEARCH DALLAS
30SALES CHICAGO
40OPERATIONS BOSTON
我们看到40部门是存在的,但是等值连接里并不能显示这样的记录,道理很简单,40部门没有一个员工,所以在和员工表做等值连接的时候无法显示出来,那如何来实现这样的需求呢?
很简单 Oracle提供了(+)这样的外连接方法
我们来看下面的句子:
scott@DB01>selectd.deptno,
2 d.dname,
3 d.loc,
4 e.empno,
5 e.ename
6 fromemp e,
7 deptd
8 whered.deptno=e.deptno(+);
DEPTNODNAME LOC EMPNOENAME
---------------------------------------------------------
10ACCOUNTING NEWYORK 7782CLARK
10ACCOUNTING NEWYORK 7839KING
10ACCOUNTING NEWYORK 7934MILLER
20RESEARCH DALLAS 7566JONES
20RESEARCH DALLAS 7902FORD
20RESEARCH DALLAS 7876ADAMS
20RESEARCH DALLAS 7369SMITH
20RESEARCH DALLAS 7788SCOTT
30SALES CHICAGO 7521WARD
30SALES CHICAGO 7844TURNER
30SALES CHICAGO 7499ALLEN
30SALES CHICAGO 7900JAMES
30SALES CHICAGO 7698BLAKE
30SALES CHICAGO 7654MARTIN
40OPERATIONS BOSTON
我们看到40部门(OPERATIONS)被显示出来了,当然由于他没有员工所以在员工信息部分会补空值。
也许有人会有这样的疑问,whered.deptno=e.deptno(+)中的(+)符号是应该出现在那个表的列后面呢?
是随意的吗?
我们来稍微总结一下:
1.(+)只能在关联条件的一端出现,本例中是e.deptno(+),也可以说是e表的一边
2.如果在where后面有其他的条件,那其他的所有条件都要使用(+)
3.(+)出现在值少的一端,比如本例中,员工表没有员工能和40部门对应,我们认为员工表记录少
我们来看部门表记录少的一个例子:
scott@DB01>updateempsetdeptno=nullwhereempno=7369;
1rowupdated.
scott@DB01>commit;
Commitcomplete.
scott@DB01>select*fromemp;
EMPNOENAME JOB MGRHIREDATE SAL COMM DEPTNO
---------------------------------------------------------------------------------
7369SMITH CLERK 790217-DEC-80 800
7499ALLEN SALESMAN 769820-FEB-81 1600 300 30
7521WARD SALESMAN 769822-FEB-81 1250 500 30
7566JONES MANAGER 783902-APR-81 2975 20
7654MARTIN SALESMAN 769828-SEP-81 1250 1400 30
7698BLAKE MANAGER 783901-MAY-81 2850 30
7782CLARK MANAGER 783909-JUN-81 2450 10
7788SCOTT ANALYST 756619-APR-87 3000 20
7839KING PRESIDENT 17-NOV-81 5000 10
7844TURNER SALESMAN 769808-SEP-81 1500 0 30
7876ADAMS CLERK 778823-MAY-87 1100 20
7900JAMES CLERK 769803-DEC-81 950 30
7902FORD ANALYST 756603-DEC-81 3000 20
7934MILLER CLERK 778223-JAN-82 1300 10
通过修改,我们把7369(SMITH)的部门号置为空,这就意味着,当我们使用部门号deptno做等值查询时,7369不会显示出来,因为部门表没有一条记录可以和7369对应,现在的情况是员工表记录多,如果我们想要把7369显示,就要在少的一端(部门表列)上添加(+)
scott@DB01>selectd.deptno,
2 d.dname,
3 d.loc,
4 e.empno,
5 e.ename
6 fromemp e,
7 deptd
8 whered.deptno(+)=e.deptno;
DEPTNODNAME LOC EMPNOENAME
------------
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 等值 连接 操作