Oracle多表查询

本节剖析Oracle多表查询中的相关内容。

本文所用到的表为oracle中scott用户下的emp表，dept表，数据如下：

 

 

一、笛卡尔集：

概念：假设A,B是2个集合，A X B所形成的集合叫笛卡尔集。

实例1：A={a,b} B={c,d,e},那么A X B={(a,c),(a,d),(a,e),(b,c),(b,d),(b,e)}

图形实例如下：

A：  B：

那么A X B:

由此可看出笛卡尔集的结果为列数相加行数相乘，其中有很多重复项。

那么在多表查询中如果查询出来的结果有重复项，通常会对我们需要的结果造成干扰，为了避免这种笛卡尔集的出现，可以添加有效的连接条件，如果有n张表，那么连接条件最少为n-1个。

二、交叉连接：

在oracle中笛卡尔集也有对应的求解，称为交叉连接cross join,示例如下:

Example:

select d.dname,e.ename,d.deptno,e.deptno from dept d cross join emp e;

 emp表14条数据，dept表4条数据，14x4=56刚好等于笛卡尔集的结果。

三、自然连接：

 概念：natural join是一种特殊的等值连接，将表中具有相同名称的列进行自动匹配，不需要再加等值条件。

Example:

select ename,deptno,dname from emp natural join dept;

可以看到dname是dept表中的列，条件中并没有加入emp.deptno=dept.deptno，但是结果仍然正确，这就是自然连接的作用，特别注意的是需要列名相同才可以。

四、内连接：

概念：inner join只返回满足条件的相匹配的查询结果,inner可省略。

（1）第一种用using子句建立连接

Example:

select ename,deptno,dname from emp join dept using(deptno);

（2）第二种：用on子句建立连接：

Example：

select ename,e.deptno,dname from emp e join dept d on e.deptno=d.deptno;

这2种都可以查询到我们想要的结果，那么这2种语句的意义在哪里呢，刚才有讲过自然连接会将列名相同作为条件，

如果此时2张表中有多个列值对应相等，那么oracle会自动将这些列都作为条件，而我们有可能只需要其中的一部分

作为条件，那么此时就用到了这2种语句，也是它们存在的价值。

五、外连接

概念：分为左外连接，右外连接，完全（外）连接，它们都返回满足结果的连接，不同的是左外连接还返回不满足左表的部分数据，

同样的右外连接还返回不满足右表的部分数据，完全连接则还返回不满足条件的部分数据，也就是说，左外连接返回左表的全部数据

右表的满足条件数据，右外连接返回右表的全部数据左表满足条件的数据，完全连接返回左右表的全部数据。

Example:

select ename,d.deptno,dname from dept d left join emp e on d.deptno=e.deptno;

select ename,d.deptno,dname from emp e right join dept d on e.deptno=d.deptno;

右外连接可以看做是将左外连接的表换了个位置，查询结果相同，所以此处只讲左外连接，dept表中有deptno=40的部门，但是

在emp表中并没有deptno=40的员工，如果此处没有用左外连接，那么就不会显示最后一行，如下图：

select ename,d.deptno,dname from dept d join emp e on d.deptno=e.deptno;

可以看到并没有deptno=40的那一行。

Example2:

select e.ename,d.dname from emp e full join dept d on e.deptno=d.deptno;

完全外连接可以看做是将左外连接和右外连接进行了一个“合体”，不满足的结果也会显示出来，可以看出来左外连接用到的情况最多。

另外，左外连接和右外连接还有另一种表达的方式，使用(+)操作符，

Expression:左外连接：select table1.column,table2.column from table t1,table2 t2 where t1.column = t2.column(+)

右外连接：select table1.column,table2.column from table1 t1,table2 t2 where t1.column(+) = t2.column

Example：

select e.ename,d.dname from dept d,emp e where e.deptno(+) = d.deptno;

select e.ename,d.dname from emp e ,dept d where e.deptno(+)=d.deptno;

所查结果一样，这里我故意将条件的位置写反，说明并不是(+)的位置固定，而是要根据条件中的表的名字的位置判断，这也是外连接查询的一种写法，注意此处是where子句不再是on子句。

 六、自连接：

概念：自连接很容易理解，就是同一张表，将它看做是2张一模一样的表，其中一张表的列可以作为另一个表的查询条件。

Example：在emp表中查询员工自己的上司。

select e.ename,m.ename from emp e,emp m where e.mgr=m.empno;

分析：emp e表中的mgr是他的上司的empno，而他的上司的empno也在emp这张表中，这就将同一张表看成了2张表。

 七、set运算符：

概念：专门用来合并多条select语句的结果，分为四种。

左图表示2个查询select1和select2，其中B是2个查询的重复数据。

1、union操作符：用于取得2个查询的并集，并且会将其中的重复数据去掉，最后按第一列的结果进行升序排序。

 用图来表示就是     select1 union select2 = A+B+C

2、union all操作符：同样用于取得2个查询的并集，但不会将重复的数据去掉，也不会进行排序。

用图来表示就是   select1 union all select2 = A+B+B+C

3、intersect操作符：同于取得2个查询的交集 ，并且会以第一列的结果升序排序   

用图表示就是：select1 intersect select2 =B

4、minus操作符：用于取得在第一个中的结果，但不在第二个中的结果，并且会以第一列的结果升序排序

用图表示就是    select1 minus select2 = A

Example:

先根据emp表和dept表做2张新表，emp01取部门号为10和20的，emp02取部门号为20和30的：

create table emp01
as
select * from emp where deptno in(10,20); 

create table emp02
as
select * from emp where deptno in(20,30);

数据如下：

union操作符的运用：

select deptno,empno,ename from emp01
union
select deptno,empno,ename from emp02;

emp表中只有10,20,30部门的数据，所以union以后应该刚好是emp表的数据。

union all操作符的运用：

select deptno,empno,ename from emp01
union all
select deptno,empno,ename from emp02;

union all以后会将重复的也显示出来，所以部门号为20的员工会显示2次

intersect操作符的运用：

select deptno,empno,ename from emp01
intersect
select deptno,empno,ename from emp02;

intersect以后会将重复数据显示，所以只显示部门号为20 的数据。

minus操作符的运用：

select deptno,empno,ename from emp01
minus
select deptno,empno,ename from emp02;

minus只显示第一个查询的结果，把其中和第二部分查询重复的删除，所以只显示10号部门不会显示20号部门的信息。

 

 

到此为止，多表查询全部总结完毕，有任何建议可以在下方评论。

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　2018-08-10    16:57:07