ORACLE 多表连接与子查询

连接的类型

Oracle8i之前的表连接：

等值连接(Equijoin)

非等值连接(Non-Equijoin)

外连接(Outer join):-->左外连接-->右外连接

自连接(Self join)

Oracle9之后新引入的连接形式(支持SQL99规范)

交叉连接(Cross join)

自然连接(Natural join)

使用Using子句建立连接

使用on子句建立连接

外连接(Outer join):-->左外连接-->右外连接-->全外连接

 

oracle中的连接可分为，

1. 内连接(自然连接)

2. 外连接

（1）左外连接 (左边的表不加限制)

（2）右外连接(右边的表不加限制)

（3）全外连接(左右两表都不加限制)

3. 自连接（同一张表内的连接）

4、交叉连接(cross join) （即 笛卡尔积）

5、其他连接定义（可能属于上面3种中的某一种）

（1）等值连接（equijoin）

（2）非等值连接(non-equijoin) 

（3）on建立查询连接

（4）using 建立查询连接

 

SQL的标准语法：

多表连接使用SQL99连接语法,两个以上的表进行连接时应依次/分别指定相临的两个表之间的连接条件.

select  字段列表   
from   table1   
[cross join table2] |   
[natural join table2] |   
[join table2 using(字段名)] |   
[join table2 on(table.column_name=table2.column_name)] |   
[(left | right | full out ) join table2   
on(table1.column_name=table2.column_name)]   
[cross join table3] |   
[natural join table3] |   
[join table3 using(字段名)] |   
[join table3 on(table.column_name=table3.column_name)] |   
[(left | right | full out ) join table3   
on(table2.column_name=table3.column_name)]...;  

inner join 表示内连接；

left join表示左外连接；

right join表示右外连接；

full join表示完全外连接；

on子句 用于指定连接条件。

in--------------等于列表中的任何一个

any--------------和子查询返回的任意一个值比较

all--------------和子查询返回的所有值比较

=--------------等于

>--------------大于

>=------------大于等于

<--------------小于

<=--------------小于等于

<>--------------不等于

 

注意：

如果使用from子句指定内、外连接，则必须要使用on子句指定连接条件；

如果使用（+）操作符指定外连接，则必须使用where子句指定连接条件; (+)在等号左边边指右连接，(+)在等号右边指左连接。

下面说明：内连接与自然连接基本相同，不同之处在于自然连接只能是同名属性的等值连接，而内连接可以使用using或on子句来指定连接条件

[1]外连接必须使用on或using子句提供相应的连接条件

[2]不能为using子句中所列举的列指定表别名，即使在group by和select子句中也是如此

[3]外连接规则：左连右补，右连左补，全连左右合并

 

1、内连接（Inner Join/Join）

Inner join逻辑运算符返回满足第一个（顶端）输入与第二个（底端）输入联接的每一行。这个和用select查询多表(select …from tablename …where ….)是一样的效果，所以内连接用的很少。还有一点要说明的就是Join 默认就是inner join。 所以我们在写内连接的时候可以省略inner 这个关键字。在where子句中指定连接条件当被连接的多个表中存在同名字段时,必须在该字段前加上"表名"作为前缀可以提高查询效率

select a.ename,b.dname from emp a,dept b where a.deptno  = b.deptno;

--或

select a.ename,b.dname from emp a inner join dept b on a.deptno  = b.deptno;

--或

select a.ename,b.dname from emp a join dept b on a.deptno  = b.deptno;

 

自然连接(Natural join) ------可理解为内连接的一种

自然连接是在两张表中寻找那些数据类型和列名都相同的字段，然后自动地将他们连接起来，并返回所有符合条件按的结果。例如下面效果是一样的：

select a.ename,b.dname from emp a  natural join dept b --自然连接

select a.ename,b.dname from emp a, dept b where a.deptno=b.deptno –非自然连接的普通where连接查询

 

2、外连接(Outer Join)

outer join则会返回每个满足第一个（顶端）输入与第二个（底端）输入的联接的行。它还返回任何在第二个输入中没有匹配行的第一个输入中的行。外连接分为三种： 左外连接，右外连接，全外连接。 对应SQL：LEFT/RIGHT/FULL OUTER JOIN。 通常我们省略outer 这个关键字。 写成：LEFT/RIGHT/FULL JOIN。

在左外连接和右外连接时都会以一张表为基表，该表的内容会全部显示，然后加上两张表匹配的内容。 如果基表的数据在另一张表没有记录。 那么在相关联的结果集行中列显示为空值（NULL）。

对于外连接， 也可以使用“(+) ”来表示。 关于使用（+）的一些注意事项：

1.（+）操作符只能出现在where子句中，并且不能与outer join语法同时使用。(+)在等号左边边指右连接，(+)在等号右边指左连接。

2. 当使用（+）操作符执行外连接时，如果在where子句中包含有多个条件，则必须在所有条件中都包含（+）操作符

3.（+）操作符只适用于列，而不能用在表达式上。

4.（+）操作符不能与or和in操作符一起使用。

5.（+）操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接

 

2.1、左外连接（Left outer join/ left join）

left join是以左表的记录为基础的,示例中Dave可以看成左表,BL可以看成右表,它的结果集是Dave表中的数据，在加上Dave表和BL表匹配的数据。换句话说,左表(Dave)的记录将会全部表示出来,而右表(BL)只会显示符合搜索条件的记录。BL表记录不足的地方均为NULL.。

用（+）来实现， 这个+号可以这样来理解： + 表示补充，即哪个表有加号，这个表就是匹配表。所以加号写在右表，左表就是全部显示，故是左连接。

查询出所有员工和其领导，最高领导没领导【左关联，则查询数据数目以左表为标准,使用left [outer] join… on， 或(+)在右表字段的最后添加】

select a.ename "下属名称",b.ename "领导名称" from emp a  left outer join emp b on a.mgr=b.empno --where b.empno is null

--或

select b.ename "下属名称",a.ename "领导名称" from emp a  right outer join emp b on b.mgr=a.empno

--或

select a.ename "下属名称",b.ename "领导名称" from emp a , emp b where  a.mgr=b.empno(+)

--或

select b.ename "下属名称",a.ename "领导名称" from emp a , emp b where  a.empno(+)=b.mgr

 

但是恰当使用子查询有利提高查询效率，如下：

select a.ename "下属名称",

(select b.ename from emp b where a.mgr=b.empno) "领导名称" from emp a 

 

2.2、右外连接（right outer join/ right join）

和left join的结果刚好相反,是以右表(BL)为基础的, 显示BL表的所以记录，在加上Dave和BL 匹配的结果。 Dave表不足的地方用NULL填充.

用（+）来实现， 这个+号可以这样来理解： + 表示补充，即哪个表有加号，这个表就是匹配表。所以加号写在左表，右表就是全部显示，故是右连接。

--查询出所有不是领导的员工，最低员工下面没有员工【右关联，则查询数据数目以右表为标准，使用 right [outer] join …on，或(+)在左表字段的最后添加】

select  a.ename "下属名称",b.ename "领导名称" from emp a  right outer join emp b on a.mgr=b.empno where a.ename is  null

--或

select  b.ename "下属名称",a.ename "领导名称" from emp a  left outer join emp b on  a.empno=b.mgr where b.ename is  null

--或

select  a.ename "下属名称",b.ename "领导名称" from emp a , emp b where a.mgr(+)=b.empno and a.ename is  null

--或

select  b.ename "下属名称",a.ename "领导名称" from emp a  , emp b where  a.empno=b.mgr(+) and b.ename is  null

 

--或使用两个集合的差值，所有员工-领导员工，但是效率很低

select * from emp where empno in(

select a.empno from emp a

minus

select distinct b.mgr from emp b )

 

 

2.3、全外连接（full outer join/ full join）

左表和右表都不做限制，所有的记录都显示，两表不足的地方用null 填充。 全外连接不支持（+）这种写法。

--使用全连接查询所有员工和对应的领导信息（全连接不足：领导和下属反回来时候，显示不准确）

select a.ename "领导",b.ename "下属" from emp a  full outer join emp b on b.empno=a.mgr 

--或（union 和union all 性能差比较：union处理方式是先union all,然后过滤重复数据，所以union all性能较好）

select a.ename "领导",b.ename "下属" from emp a  left outer join emp b on a.empno=b.mgr

union 

select b.ename "领导",a.ename "下属" from emp a  left outer join emp b on b.empno=a.mgr;

--或

select a.ename "领导",b.ename "下属" from emp a  left outer join emp b on a.empno=b.mgr

union 

select a.ename "领导",b.ename "下属" from emp a  right outer join emp b on a.empno=b.mgr;

 

3、自连接

自连接(self join)是SQL语句中经常要用的连接方式，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。

--查询出所有员工和其领导，最高领导没领导(有一条数据没有被查询出来)

select a.ename "下属名称",b.ename "领导名称" from emp a , emp b where a.mgr=b.empno

 

4、交叉连接(cross join) 

交叉连接不带WHERE 子句，它返回被连接的两个表所有数据行的笛卡尔积，返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。 

SQL语句如下： 

 

select * from emp where ename='FLJ1169';--查出一条结果数据

select * from dept;--查出4条结果数据

--交叉连接后，则多出3条交叉数据

 

select * from (select a.*,b.dname,b.deptno b_deptno from emp a cross join dept b ) where ename='FLJ1169'

 

5、其他连接定义（可能属于上面3种中的某一种）

（1）等值连接（equijoin）

使用=符号连接

（2）非等值连接(non-equijoin) 

多表中指使用除等号（=）外的其他运算符号作为连接查询。其他符号指：<、>、<=、>=、<>、between…and

（3）on建立查询连接

select a.empno,a.ename,a.sal,b.deptno,b.dname from emp a join dept b --on a.deptno=b.deptno

on (b.deptno=a.deptno);

（4）using 建立查询连接

select a.empno,a.ename,a.sal,b.dname from emp a  join dept b--on a.deptno=b.deptno  

using (deptno); 注意：但是查询结果列不能包含using条件的列，否则出错