【Oracle】多表连接查询详解

多表查询：按数据库设计原则，员工表中只有部门的编号信息，部门的详细信息会放在部门表中，这个时候我们就需要使用多表查询的功能。

课程目标：

1、什么是多表查询？

2、笛卡尔集。

3、等值连接。

4、不等值连接。

5、外连接。

6、自连接。

7、层次查询。

具体内容：

1、什么是多表查询？

2、笛卡尔集。

笛卡尔集的列数相加，行数相乘。

为了避免笛卡尔集，可以在WHERE加入有效的连接条件，如上图所示。

在实际运行环境下面，应避免使用笛卡尔全集。

在实际的应用中，连接条件至少有一个。

3、等值连接。

示例：查询员工信息，要求显示员工号、姓名、月薪、部门名称。

我们在查询多表的时候，习惯给表起一个别名。

连接符号是“=”即等值连接。

select e.empno,e.ename,e.sal,d.dname

from emp e,dept d

where e.deptno = d.deptno;

 

4、不等值连接。

示例：查询员工信息，要求显示员工号、姓名、月薪、薪水的级别。

select e.empno,e.ename,e.sal,s.grade

from emp e,salgrade s

where e.sal between s.losal and s.hisal;

注意使用between/and的时候要小值在前面，大值在后面。

若违反规定，则按下图所示，不会有查询结果。

5、外连接。

示例：按部门统计员工人数，要求显示部门号、部门名称、人数。

select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数

from emp e,dept d

where e.deptno = d.deptno

group by d.deptno,d.dname;

但是查看部门表发现，我们的部门表里面有四个部门，上图显示的结果仅仅只有三个部门，很明显是不对的。

缺少了40部门，为什么上图会漏掉这一个部门呢？

原因是部门表中存在40的部门，在员工表里面却不存在，所以不作统计。

这个时候就需要使用我们的外连接。

核心：通过外连接，把对于连接条件不成功的记录，仍然包含在最后的结果中。

左外连接：当连接条件不成立的时候，等号左边的表仍然被包含。

右外连接：当连接条件不成立的时候，等号右边的表仍然被包含。

修改之前的select语句。

右外连接：需要在“=”的左边加上“（+）”。

select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数

from emp e,dept d

where e.deptno（+） = d.deptno

group by d.deptno,d.dname;

如果是左外连接，需要在“=”的右边加上“（+）”。

6、自连接。

示例：查询员工姓名和员工的上级姓名。

select e.ename 员工姓名, b.ename 上级姓名

from emp e,emp b

where e.mgr = b.empno;

核心：通过别名，将同一张表视同为多张表。

7、层次查询。

自连接存在的问题：

不适合操作大表：产生的笛卡尔集的大小至少是一个平方（至少两个表）。

解决方法：层次查询。

层次查询是一个单表查询，只有一个表，不存在第二个表，只有在一张表的情况下才不会产生笛卡尔集。

某些情况下，可以替代自连接，本质上，它是一个单表查询。

示例：上述例子。

select level，empno,ename,sal,mgr

from emp

connect by prior empno = mgr

start with empno = 7839

order by 1;

还有一种形式 start  with mgr = null；

树的深度我们还有一个伪列：Level

思考：自连接和层次查询的优缺点各式什么？