多表查询

一、基本概念
1.多表查询就必须消除两张表之间存在的笛卡尔积，使用一个条件语句消除笛卡尔积,可以使用别名表达。
①要求显示每个雇员的编号、姓名、职位、工 资、部门名称、部门位置。

select e.empno,e.ename,e.job,e.sal ,d.dname,d.loc
from emp e,dept d
where e.deptno=d.deptno;

②查询雇员的编号、姓名、职位、工资、工资等级

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

9Y(UX@U5$QPONN3$EI1L9%G.png

③查询每个雇员的编号、姓名、职位、工资、工资等级、部门名称

select e.empno,e.ename,e.job,e.sal,s.grade,d.dname
from emp e,salgrade s,dept d
where e.sal between s.losal and s.hisal and e.deptno=d.deptno;

总结：没有关联字段或者关联条件的两张数据表是永远不可能实现多表查询的，复杂的查询分步骤进行。

二、表的连接
主要分为两种形式：
内连接(等值连接)：所有满足条件的数据都会被显示出来。
外连接(左外连接、右外连接、全外连接):控制左表和右表的数据是否全部显示。
左外连接：字段+字段（+）；
右外连接：字段（+）+字段；
①先插入一条数据测试

insert into emp(empno,ename,job)values(0001,'老张','CLERK');

②左连接

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

left.png

③右连接

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

right.png

如果发现查询的数据不完成，就使用外连接来控制，不要刻意的去管是左连接还是右连接，只要能完成显示数据就行。

三、SQL：1999语法支持
“(+)”只有oracle才有，所以可以利用SQL：1999语法来实现查询。
基本语法如下：
select [distinct]* | 列 [别名]
from 表名称1
[cross join 表名称2]
[natural join 表名称2]
[join 表名称 on(条件) | using(字段)]
[left | right | full outer join 表名称2];

①cross join 交叉连接，主要用于产生笛卡尔积，简单实现多表查询

select*  from emp cross join dept;

②natural join 自然连接,主要用于消除笛卡尔积

select* from emp natural join dept;

③using 如果说一张表内有多个关联字段存在，那么可以使用using

select* from emp join dept using(deptno);

④on 子句，没有关联字段，则可以使用on设置条件

select*
from emp e join salgrade s
on (e.sal between s.losal and s.hisal);

⑤外连接
(1)左外连接

select* from emp left outer join dept using(deptno);

②右连接

select* from emp right outer join dept using(deptno);

③全外连接

select*  from emp full outer join dept using(deptno);

四、数据的集合操作
将多个查询结果连接为一个查询结果返回
①union 将连个查询结果合并在一起

select empno,ename,job from emp where deptno=10 union select empno,ename,job from emp;

②union all 所有重复的数据都会显示出来

select empno,ename,job,deptno from emp where deptno=10 union all select empno,ename,job,deptno from emp;

③intersect 返回相同的部分，属于交集操作

select empno,ename,job from emp where deptno=10 intersect select empno,ename,job from emp;

intersect.png

④minus 返回差集，是用第一个集合减去第二个集合

select empno,ename,job from emp minus select empno,ename,job from emp where deptno=10;

minus.png

总结：只要是多表查询就一定存在笛卡尔积，无法消除，一定存在一个或多个关联字段，否则无法查询。