Oracle：多表查询

实际应用中，最重要的就是多表查询。

笛卡尔集：

多表查询的理论基础，是笛卡尔兄弟发明的。

比如，有两张表，第一张表有2行2列，第二张表有4行4列，笛卡尔集产生的行数是2*4=8行，列数就是2+4=6列。

但是得出的笛卡尔集并不都是对的。例如第二条数据，黄色部门号跟蓝色部门号不一致，就是错的。

我们要选出正确的数据。所以链接条件就是emp.deptno=dept.deptno。

连接条件和表的个数是有关系的，如果当前是两张表，那么链接条件就只有一个。

链接条件至少有n-1个，n是表的个数。

 

链接的类型：等值连接，不等值连接，外连接，内连接。这个划分是根据连接条件划分出来的。

 

等值连接：连接条件如果中间是一个等号，就是等值连接。

不等值连接：如果条件中间不是一个等号，就是不等值连接。

 

--等值连接

--查询员工信息：员工号姓名月薪部门名称，部门名称在部门表dept中，其他的在员工表emp中，所以涉

--及到了等值连接。多表查询习惯为表起个别名。

select e.empno,e.ename,e.sal,d.dname fromemp e,dept d where e.deptno=d.deptno;

 

--不等值连接

--查询员工信息：员工号姓名月薪工资级别，工资级别表是salgrade。

select e.empno,e.ename,e.sal,s.grade fromemp e,salgrade s where e.sal between s.losal and s.hisal;

 

外连接

希望能在select的最后结果中，包含一些不成立的记录。

包括左外连接和右外连接。

左外连接：where e.deptno=d.deptno不成立的时候，等号左边代表的表仍然被包含

         写法：wheree.deptno=d.deptno(+)

右外连接：where e.deptno=d.deptno不成立的时候，等号右边代表的表仍然被包含

         写法：wheree.deptno(+)=d.deptno

 

--查询部门号部门名称人数

Select d.deptno,d.dname,count(e.empno) fromemp e,dept d where e.deptno(+)=d.deptno group by d.deptno,d.dname;

如果不加外连接，只查出三条记录。

 

自连接

就是通过表的别名，将同一张表视为多张表。

比如要查询 ***的老板是***

但是在员工表中，既有员工的编号empno，又有老板的引用mgr，这两个数据又都需要查询，就涉及到了自连接。

将一张表看作两张表：select e.ename||’的老板是’||b.ename from emp e,emp b where e.mgr=b.empno;

自连接有性能问题，自连接就涉及到多表，就要生成笛卡尔集。如果表中有1亿条记录，生成的笛卡尔集就有1亿*1亿条记录，生成的表就太大了。参见Oracle优化文档第5点。

为了解决这个问题，用到了另外的操作，层次查询。

如果查询的条件满足一棵树的时候，就可以使用层次查询。例如上面的查询就可以产生一棵树：

用level表示层次，也是层次查询中的一个伪列，在执行层次查询的时候自动添加的一列，可以表示树的深度。上面那一层的员工号等于下面一层的老板号。树的遍历从KING开始。实际上可以从树中的任何一个位置开始。层次

connect by prior empno=mgr start with mgris null;

上面这句话的意思是：prior表示前驱，prior empno=mgr的意思是上一层的员工号empno等于下一层的老板号mgr。start with定义了遍历树的起始节点，条件为mgr is null，就是说从mgr为Null的那个节点开始，也就是KING节点。

整条sql语句是：

Select level,empno,ename,mgr from empconnect by prior empno=mgr start with mgr is null order by 1;

因为层次查询只有一张表，所以不会产生笛卡尔集。

层次查询的缺点就是得到的结果不直观。