oracle数据库表左连接右连接全连接的认识.docx
《oracle数据库表左连接右连接全连接的认识.docx》由会员分享，可在线阅读，更多相关《oracle数据库表左连接右连接全连接的认识.docx（11页珍藏版）》请在冰点文库上搜索。
oracle数据库表左连接右连接全连接的认识
1.首先还是说明一下，为什么要使用连接（join）？
原因有两点：
1）经常有需要访问多个表中字段的情况
2）用子查询也可以实现上面的功能，但是效率相当低下。
使用连接可以大大提高效率。
2.连接（Join）的基本结构：
select...fromtable1t1jointable2t2onti.id=t2.id;
join前后是两个需要连接的表，on表示的是连接的条件。
传统的写法是不使用join关键字，使用where表示条件：
select...fromtable1t1,table2t2whereti.id=t2.id;
一般是两个表进行连接，三个表的连接语法稍有不同:
普通写法：
select...fromajoinbona.id=b.id
joinconb.id=c.id;
传统写法：
select...froma,b,cwherea.id=b.idandb.id=c.id;
3.连接的分类：
1.对等连接
上面的就是一种对等连接，即默认的join方式。
特点是只显示连接的表中存在而且相等的记录。
其它的
记录均不显示。
传统的写法也是一种对等连接，只显示匹配条件的记录。
2.左/右连接
有时需要将进行连接的其中一个表作为基准显示全部的数据，根据连接条件在其它表中可能会出现无法
匹配的数据，此时就用空值来代替。
比如显示员工信息时，有的员工可能还没有分配到某个具体的部门
（新进员工），其所属部门一项就没有数据，但是员工仍然需要显示，即员工表数据需要全部显示。
具体语法：
select...fromtable1t1leftjointable2t2onti.id=t2.id;--左连接
或
select...fromtable1t1rightjointable2t2onti.id=t2.id;--右连接
左还是右的区别，简单地说左（右）连接就是指join的左（右）边是基准表，全部数据都要显示，根据
连接条件无法进行连接的记录就用空值代替。
比如:
selecte.empno,e.ename,d.dnamefromempeleftjoindeptdone.deptno=d.deptno;
还没有分配到某个具体的部门的员工的e.deptno肯定是没有值的，无法满足“e.deptno=d.deptno”的
条件，但由于是左连接，左边的员工表的数据必然会显示，这时该员工的部门名称就是空值。
若是rightjoin...on，就表示部门表的数据全部显示，员工表中无法连接的数据就是空值代替，意思
就是说这个部门还没有员工，因为在员工表中没有一条记录的deptno与该部门的deptno相匹配（可能是
刚刚成立的新部门）
3.全连接
简单说就是左右连接的全部表示，根据连接条件在任意一方表中出现无法匹配的情况，不满足条件的部
分均用空值代替。
具体语法：
select...fromtable1t1fulljointable2t2onti.id=t2.id;
用2中例子来说，可能有时候需要把整个公司的员工和部门信息做一个总览，可以对员工表和部门表做一
个全连接：
selecte.empno,e.ename,d.dnamefromempefulljoindeptdone.deptno=d.deptno;
就可以知道员工所属的部门以及部门下面的员工，那些新进员工和刚刚成立的新部门也会显示在其中，
也就是结果中有空值的部分。
但需要注意的是，全连接的效率比较低，通常不推荐使用。
PS：
在Oracle中，对于左/右连接和全连接还有一种特殊的表示方法，使用“（+）”符号，例如：
select...fromtable1t1,table2t2whereti.id=t2.id（+）;--左连接
select...fromtable1t1,table2t2whereti.id（+）=t2.id;--右连接
select...fromtable1t1,table2t2whereti.id（+）=t2.id（+）;--全连接
4.自然连接
这是Oracle的一种特有的连接方式。
它自动连接两个表中数据类型和名称相同的字段，然后根据条件自
动地将他们连接起来。
具体语法：
select...fromtable1t1leftnaturaljointable2t2;
比如：
selectemp.ename,dept.dnamefromempnaturaljoindept;
这里并没有指定连接的条件，实际上oracle自动的将员工表中的deptno和部门表中的deptno做了连接。
也就是实际上相当于：
selectemp.ename,dept.dnamefromempjoindeptonemp.deptno=dept.deptno;
因为这两张表的这两个字段deptno的类型和个名称完全相同。
所以使用naturaljoin时被自然的连接在
一起了。
PS：
如果自然连接的两个表仅是字段名称相同，但数据类型不同，那么将会返回一个错误。
5.自连接
简单地说就是自己和自己进行连接，连接的条件就是本表的主键。
通常认为在数据库中存在父子关系
时，应该设计成两张表。
但如果父子都是同一实体，就可以简化设计成一张表。
比如，查看员工及直接
上级的名字:
（上级也是员工，上级的数据也是员工数据，也应该存储在员工表中。
）
selectyg.empnoas员工,yg.enameas工号,sj.enameas上级fromempyg,empsjwhere
yg.mgr=sj.empno;
员工工号上级
7369SMITHFORD
7499ALLENBLAKE
7521WARDBLAKE
7566JONESKING
7654MARTINBLAKE
7698BLAKEKING
7782CLARKKING
7788SCOTTJONES
7844TURNERBLAKE
7876ADAMSSCOTT
7900JAMESBLAKE
7902FORDJONES
7934MILLERCLARK
连接的表其实就是员工表自身，因为上级也是员工，上级的empno应该与其直接下属员工的mgr相匹配。
这里实际上实现的是一种树形的结构，即多级的分类结构。
每层都是同样级别的员工，上层为其直属上
级，下层为其直属员工。
通过结果可以很清楚地看到这一结构，对于JONES来说，他的上级KING，BLAKE
的下级又有SCOTT和FORD。
只要是相同类型实体的多级的分类结构，都可以使用一张表来存储。
在实体的所有属性基础上，在添加
一个所属上级的ID即可。
比如，大小类，多级菜单，组织机构都可以采用这样的方式设计表结构。
PS：
什么时候用别名？
通常下面的情况下需要用到别名
1）实际的表名过长，编写不方便，使用别名简化。
2）同一张表检索多次时，“必须”使用别名。
上面的自连接中，连接的表其实就是一张表，不加上别名
就无法知道字段是属于哪一个引用的表。
6.交叉连接
就是两个表在数据库上的排列组合，效果就是笛卡尔乘积。
一个n行数据的表与一个m行数据的表进行交
叉连接之后，结果中包含n*m行数据。
具体语法：
select*fromempcrossjoindept;
或
select*fromemp,dept;
在实际工作中，交叉连接可用于生成调查表。
比如：
selectemp.empno,emp.ename,dept.dname,null评分fromdept,emporderby
emp.ename,dept.dname;
这样能够产生一个每个员工对所有的部门的一个评价表格：
EMPNOENAMEDNAME评分
7876ADAMSACCOUNTING
7876ADAMSOPERATIONS
7876ADAMSRESEARCH
7876ADAMSSALES
7499ALLENACCOUNTING
7499ALLENOPERATIONS
7499ALLENRESEARCH
7499ALLENSALES
7698BLAKEACCOUNTING
7698BLAKEOPERATIONS
7698BLAKERESEARCH
7698BLAKESALES
7782CLARKACCOUNTING
7782CLARKOPERATIONS
7782CLARKRESEARCH
7782CLARKSALES
...
每个员工和每个部门都会毫无遗漏地出现在结果中。
PS：
若有表t1,t2,t3,...,tn，其数据行数为r1,r2,r3,...,rn，则将t1,t2,t3,...,tn进行交
叉连接连接后的结果中会存在r1*r2*r3*...*rn条数据。
也就是说，进行交叉连接的表的数
据量如果比较大，结果会产生海量的数据。
注：
以上数据参考
4.--建立测试数据
createtablea（idnumber）;
createtableb（idnumber）;
insertintoavalues
（1）;
insertintoavalues
（2）;
insertintoavalues（3）;
insertintobvalues
（1）;
insertintobvalues
（2）;
insertintobvalues（4）;
commit;
--1.左:
--主流数据库通用的方法
select*fromaleftjoinbona.id=b.id;
--Oracle特有的方法
select*froma,bwherea.id=b.id（+）;
ID ID
--------------------
1 1
2 2
3
--2.右：
--主流数据库通用的方法
select*fromarightjoinbona.id=b.id;
--Oracle特有的方法
select*froma,bwherea.id（+）=b.id;
ID ID
--------------------
1 1
2 2
4
--3.内
--主流数据库通用的方法
select*fromajoinbona.id=b.id;
--where关联
select*froma,bwherea.id=b.id;
ID ID
--------------------
1 1
2 2
--4.全外
--主流数据库通用的方法
select*fromafulljoinbona.id=b.id;
--Oracle特有的方法
select*
froma,b
wherea.id=b.id（+）
union
select*
froma,b
wherea.id（+）=b.id;
ID ID
--------------------
1 1
2 2
3
4
--5.完全，也叫交叉连接或者笛卡尔积
--主流数据库通用的方法
select*froma,b;
--或者
select*fromacrossjoinb;
ID ID
--------------------
1 1
1 2
1 4
2 1
2 2
2 4
3 1
3 2
3 4
5.连接注解
--内连接和where相同
innerjoin
--左向外连接，返回左边表所有符合条件的
leftjoin
--右向外连接，返回右边表所有符合条件的
rightjoin
--完整外部连接，左向外连接和右向外连接的合集
fulljoin
--交叉连接，也称笛卡儿积。
返回左表中的每一行与右表中所有行的组合
crossjoin
--补充：
--左向外连接，返回左边表所有符合条件的，
--注意这里没有第二个加号，会直接过滤掉数据，只显示符合条件的记录
select*
froma,b
wherea.id=b.id（+）
andb.id=2;
ID ID
--------------------
2 2
--左向外连接，返回左边表所有符合条件的
--注意where上第二个加号，它的作用是修改右边表记录的显示，例如如果b.id（+）=2，显示为2，否则显示null
select*
froma,b
wherea.id=b.id（+）
andb.id（+）=2;
ID ID
--------------------
2 2
3
1
注：
以上资料来源于
注：
以上资料来源于网络，本人觉学习后获益匪浅，特摘录供大家互相学习，不含任何商业目的。
注明出处，说明版权归以上作者所有，并表示对作者的尊重。
谢谢阅读！
升级会员 