【Oracle】数据库多表查询

多表查询

1.多表查询的实现形式。
2.笛卡尔积的处理。
3.数据表的连接操作。
4.数据的集合操作。

多表查询
在之前的查询里，from子句里只设置了一张数据表，如果现在需要同时从多张数据表里取出数据就就属于多表查询。
基本形式：
SELECT * FROM 表名称[别名]，表名称[别名]，表名称[别名]… WHERE 过滤条件 ORDER BY 字段[ASC|DECS].
下面观察一个现象：

两表一起查询的时候行数是两表行数的乘积emp表的数据被重复的显示了4次（也就是dept表的数据量）。
可以看到，以上的数据库看成=是集合的话，他们出现了乘积的概念，这种关系在数据库上称为笛卡尔积问题。现在如果想消除掉笛卡尔积的问题必须为两张表设置关系。
我们想两张表成为这样一个关系：

即雇员的deptno=部门的deptno
因为不同的表可能会存在相同名称的列名，此时可以在列名前加上表名称：表名.列名。
SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;

这样就变为了12行且雇员的部门编号也消除，但是以上代码只是消除了显示的笛卡尔积，而在数据库的多表查询之中，笛卡尔积会一直存在，只要是存在数据表那么会存在笛卡尔积。而且一般在设计表的时候往往同样的列名称会成为关联字段。
例：
观察多表查询带来的问题：
在Oracle数据库里的sh用户下：
select * from sales,costs where sales.prod_id=costs.prod_id;
执行了的话会发现运行时间十分的长，因为从750亿条数据中筛选了100万条。
多表查询性能是很差的，在开发过程之中一定要尽可能回避。但是在进行列访问的时候使用了表名称，这样也有不好的地方，万一表名称很长呢？这时候建议使用别名。
例：使用别名
SELECT * FROM emp e,dept d WHERE e.deptno=d.deptno;

任何情况下，如果要实现多表查询操作，永远都有一个前提：要关联的数据表一定要存在关联字段或者关联条件，不满足这些要求的不能使用多表查查询。
例：显示每个雇员的编号，姓名，职位，部门名称和部门位置；
SELECT e.empno,e.ename,e.job,d.dname,d.loc FROM emp e,dept d WHERE e.deptno=d.deptno;

下面看一个三表查询的操作：
查询每个雇员的编号，姓名，职位，工资,工资等级，部门名称：
首先确定需要用到的表：
emp，dept，salgrade
再确定关联条件：
emp.deptno = dept.deptno;
emp.sal BETWEEN salgrade.losal AND salgrade.hisal;
一般情况下，多个消除笛卡尔积的条件都使用and进行里连接。
SELECT e.empno,e.ename,e.job,e.sal,s.grade,d.dname FROM emp e,dept d,salgrade s WHERE e.deptno=d.deptno and e.sal between s.losal and s.hisal;

多表查询操作的注意事项：
1.没有关联字段的两张数据表是永远不可能实现多表查询操作的。
2.在进行多表查询的复杂查询操作的时候，建议分步骤分析，分布解决问题。

表的连接

既然可以连接多张数据表进行查询，那么在进行表连接的时候需要设置一个数据主控方，对于这样的连接主要分为两种形式：
1.内连接（等值连接）：所有满足条件的数据都会被显示出来；
2.外连接（左外连接、右外连接、全外连接）：控制左表与右表的数据是否全部显示。
为了描述现在的问题，下面向emp表里加入一行数据：
INSERT INTO emp (empno,ename,job) VALUES (8888,’李晔’,’CLERK’);

1.内连接：
之前使用的都是等值连接。
若条件子句为：WHERE emp.deptno=dept.deptno, 那么只有当这个条件满足之后才会显示出对应的数据，如果此时数据为空，那么条件无法满足，内容无法显示。这时候想显示出李晔这个内容该怎么办呢？
2.外连接：
让等值判断左右两边有一边的数据可以完全显示出来，如果要实现外连接则要使用（+）的语法完成。
左外连接：字段=字段（+）；
右外连接：字段（+）=字段；
这是一个左外连接：
SELECT *FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO(+);

可以看到：李晔的信息即使没有部门编号也被显示了出来。
这是一个右外连接：
SELECT *FROM EMP E,DEPT D WHERE E.DEPTNO（+）=D.DEPTNO;

可以看到虽然没有雇员是部门40的，但是40部门的信息还是显示了。
在使用外连接的过程中，不要刻意区分左右连接，一切以查询结果为主，如果等值查询中缺少了内容则使用外连接。
例：
查询雇员的姓名和领导姓名。
分析：需要emp表1，emp表2.
关联字段：雇员的mgr=雇员的empno。
select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;

发现一个问题:数据显示不完整：李晔和KING的数据不完整。
改进： select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno(+);

以上例题的这种自己关联自己的查询也称为自身关联，但是不管是不是自身关联，程序认的是from后存在几张表，只要是多张表那么就得消除笛卡尔积。

（+）标记是Oracle才有的，其他数据库用什么呢？所以可以利用SQL1999语法来实现查询。
整个SQL1999语法的多表查询语法如下：
SELECT [DISTINCT] * |列[别名]
FROM 表名称1
[CROSS JOIN 表名称2]
[NATURAL JOIN 表名称2]
[JOIN 表名称 ON（条件）|USING （字段）]
[LEFT|RIGHT|FUL OUTER JOIN 表名称2];
以上语法由多个部分组成，下面拆分来观察。
1.交叉连接：CROSS JOIN,主要的功能是产生笛卡尔积，简单的实现多表查询；
2.自然连接：自动使用关联字段消除笛卡尔积（一般的关联字段是外键，此处以，名字相同为主，属于内连接的概念），默认情况下会将关联字段设置在第一列上，重复的内容不再显示。

3.USING子句：若一张表里有多个关联字段，那么可以使用USING来明确的设置一个关联字段。
SELECT * FROM emp JOIN dept USING(deptno);
4.ON子句：若没有关联字段，则可以使用ON子句设置条件：
SELECT * FROM emp JOIN salgrade ON (emp.sal BETWEEN salgrade.losal AND salgrade.hisal);
5.外链接：在SQL1999里明确定义了左外连接，右外连接，全外连接
例：左/右外连接：
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);
只有在SQL1999里才明确的可以使用全外连接，但是这样的操作几乎是没有意义的。

数据集合操作

之前都属于多张数据表的关联操作，但是数据的集合操作主要负责连接的是查询结果，提供有4种操作符：UNION, UNION ALL, INTERSECT,MINUS利用这几个符号可以实现多个查询语句的连接。
格式如下：
SELECT * …
FROM …
WHERE…
ORDER BY…
UNION|UNION ALL|INTERSECT|MINUS
SELECT * …
FROM …
WHERE…
ORDER BY…
UNION|UNION ALL|INTERSECT|MINUS
SELECT * …
FROM …
WHERE…
ORDER BY…
UNION|UNION ALL|INTERSECT|MINUS
…
相当于把多个查询结果连接为一个查询结果返回，那么也就有一个要求：多个查询结果返回的列的结果必须相同。
UNION
例：
SELECT * FROM emp WHERE deptno=10 UNION SELECT *FROM emp;

此时将两个查询结果合并在了一起，但是union的操作特点是如果遇见了相同的内容不会重复显示。
UNION ALL
SELECT * FROM emp WHERE deptno=10 UNION ALL SELECT *FROM emp;

可见union的操作特点是如果遇见了相同的内容会重复显示。
INTERSECT
SELECT * FROM emp WHERE deptno=10 INTERSECT SELECT *FROM emp;

此处返回了相同的数据部分属于交集
MINUS
SELECT * FROM emp MINUS SELECT *FROM emp WHERE deptno=10;、

经过分析可知：返回的结果是差集，利用第一个集合-第二个集合。

小结：
只要是多表查询就一定会存在笛卡尔积，所以优秀的系统设计绝对不可能取去考虑多表查询。
多表连接查询时，必须存在关联字段。
大部分使用的是内连接，Oracle提供的外链接控制是（+）。
在Oracle外的数据库必须利用SQL1999语法实现外连接操作
可以使用集合操作将多个查询结果合并到一起显示，但是要求返回的结构必须相同。