oracle学习笔记五——查询之多表查询
五、多表查询
提到多表查询，自然不得不说的就是：笛卡尔集
概念不多说，自行百度，下图理解清楚了也就大体知道什么是笛卡尔集：
笛卡尔集产生原理
1.笛卡尔集会在下面条件下产生:
（1）省略连接条件
（2）连接条件无效
（3）所有表中的所有行互相连接
2.为了避免笛卡尔集， 可以在 WHERE 加入有效的连接条件。
3.如果要进行n个表的连接那么就至少添加n-1个连接条件
补：Oracle中的sql优化
3.在实际运行环境下，应避免使用全笛卡尔集。
1.验证笛卡尔集的产生
（1）省略连接条件
SQL> select count(*) from emp;
-->结果：14
SQL> select count(*) from dept;
-->结果：4
SQL> select count(*) from emp,dept;
-->结果：56 （=14*4）
（2）连接条件无效
SQL> select count(*) from emp e,dept d where e.ename is not null;
-->结果：56 （=14*4）
-->说明：这里where后的条件一点意义都没得
（3）所有表中的所有行互相连接
-->这就是笛卡尔集的定义
2.多表查询
（1）等值查询
--等值连接
--查询员工信息： 员工号 姓名 月薪 部门名称
SQL> select e.empno,e.ename,e.sal,d.dname
from emp e,dept d
where e.deptno=d.deptno;
（2）不等值查询
--不等值连接
--查询员工信息： 员工号 姓名 月薪 工资级别
SQL> select e.empno,e.ename,e.sal,s.grade
from emp e,salgrade s
where e.sal between s.losal and s.hisal;
等价于
SQL>select e.empno,e.ename,e.sal,s.grade
from emp e,salgrade s
where e.sal between s.losal and s.hisal;
注：
这里有几个知识点：
1.使用别名可以简化查询。
2.使用表名前缀可以提高执行效率。
3.如果使用了表的别名，则不能再使用表的真名。
4.between…and…两边是闭区间
（3）外连接
--理解下面这个例子，了解为什么使用外连接
--（未使用外连接）按部门统计员工人数： 部门号 部门名称 人数
SQL> select d.deptno,d.dname,count(e.empno)
from emp e,dept d
where e.deptno=d.deptno
group by d.deptno,d.dname;
-->结果：
未使用外连接（少了一个部门）
注：
这里少了一个部门OPERATIONS，虽然他没有人，但应该表示为0才对，这时候外连接就有用武之地了。
--（使用外连接）按部门统计员工人数： 部门号 部门名称 人数
SQL> select d.deptno,d.dname,count(e.empno)
from emp e,dept d
where e.deptno(+)=d.deptno
group by d.deptno,d.dname
order by 1;
-->结果：（现在的结果应该是期望的了）
使用外连接（40号部门出现了）
注：关于外连接：
1.外连接的目的：对于不成立的记录，仍然希望包含在最后的结果中
2.左外连接: where e.deptno=d.deptno 不成立时，等号左边所代表的仍然被包含
写法: where e.deptno=d.deptno(+)
3.右外连接: where e.deptno=d.deptno 不成立时，等号右边所代表的表仍然被包含
写法: where e.deptno(+)=d.deptno
需要注意的是(+)在语句中“=”的位置
（4）自连接
--自连接
--查询员工信息： ***的老板是***
--通过表的别名，将同一张表视为多张表，还是多表的操作
SQL> select e.ename||'的老板是'||b.ename
from emp e,emp b
where e.mgr=b.empno;
注：
自连接的缺点：自连接一定会产生笛卡尔集，因此不适合操作大表。
解决办法，或者说替代方案：层次查询（下面说）
--执行层次查询
SQL> select level,empno,ename,mgr
from emp
connect by prior empno=mgr
start with mgr is null
order by 1;
注：
1.层次查询不用 where 表示条件，用connect by表示，start with 是从树的那个节点开始查询（start with empno=7369）这样查出来的是7869为根节点的子树。如果是从根节点也可以用start with mgr is null; 来查询出所有节点的结果。当然，也可以对层次查询的深度进行表示，比如用level来表示层次深度。
2.层次查询只涉及一张表，不产生笛卡尔积，但得到的结果可能不太直观（有得必有失）。
3.此层次查询的示意图：
层次查询示意图
3.补充SQL99标准的连接语法（熟悉其他数据库的应该很熟悉）
（1）叉集（cross join)
--等值连接
--查询员工信息： 员工号 姓名 月薪 部门名称
SQL> select e.empno,e.ename,e.sal,d.dname
from emp e
cross join dept d
where e.deptno=d.deptno;
注：
1.使用CROSS JOIN 子句使连接的表产生叉集。
2.叉集和笛卡尔集是相同的。
（2）natural join子句（限制：两张表至少有一列同名）
--等值连接
--查询员工信息： 员工号 姓名 月薪 部门名称
SQL> select e.empno,e.ename,e.sal,d.dname
from emp e
natural join dept d;
-->结果和叉集的结果相同
注：
1.NATURAL JOIN 子句，会以两个表中具有相同名字的列为条件创建等值连接。
2.在表中查询满足等值条件的数据。
3.如果只是列名相同而数据类型不同，则会产生错误。
（3）INNER JOIN 内连接（inner可以省略）
--内连接只返回满足连接条件的数据，不显示。
1）using子句为join子句指定连接用到的列
--等值连接
--查询员工信息： 员工号 姓名 月薪 部门名称
SQL> select e.empno,e.ename,e.sal,d.dname
from emp e
join dept d
using(deptno);
2）on子句为join子句指定连接条件（这个更常用）
SQL> select e.empno,e.ename,e.sal,d.dname
from emp e
join dept d
on e.deptno=d.deptno;
注：你应该使用on
1.可以使用 ON 子句指定额外的连接条件。
2.这个连接条件是与其它条件分开的。
3.ON 子句使语句具有更高的易读性。
4.using 可以在有多个列满足条件时进行选择，但不能给选中的列中加上表名前缀或别名（这个极不好用）。
（4）外连接
1）左外连接(letf outer join)
SQL> select d.deptno,d.dname,count(e.empno)
from dept d
left outer join emp e
on d.deptno=e.deptno
group by d.deptno,d.dname
order by 1;
2）右外连接(right outer join)
SQL> select d.deptno,d.dname,count(e.empno)
from emp e
right outer join dept d
on d.deptno=e.deptno
group by d.deptno,d.dname
order by 1;
3）满外连接(full outer join)
SQL> select d.deptno,d.dname,count(e.empno)
from emp e
full outer join dept d
on d.deptno=e.deptno
group by d.deptno,d.dname
order by 1;
注：
1.left/right/full outer join的outer都可以省略，效果相同
2.oracle中的full outer join，是在等号两边都加上“+”，如：where e.deptno(+)=d.deptno(+)
3.在外连接中，如果连接条件在两张表中的字段名一样，也可以用using指定列，如：
SQL> select e.empno,e.ename,d.dname
from emp e
right outer join dept d
using(deptno)
order by 1;
-->再强调一下：using不能给选中的列中加上表名前缀或别名（这个极不好用）。
