多表查询

1、多表查询的基本语法

　　多表查询指的是从多张数据表之中取得数据的一种操作，那么此时只需要在FROM子句之后定义多个数据来源即可，所以SQL的语法结构如下：

SELECT [DISTINCT]*|列[别名], 列|[别名], ....FROM 数据表[别名], 表名称[别名], 表名称[别名] ....[WHERE 条件(s)][ORDER BY 字段[ASC|DESC]], 字段[ASC|DESC....];

　　但是进行多表查询的具体操作之前，首先来学习一个COUNT()函数，此函数的功能是可以用于统计数据表之中的数据行的个数。
范例：查询emp表中的数据量 – 14条记录

SELECT COUNT(*) FROM emp;

范例：查询dept表中的数据量 – 4条记录

SELECT COUNT(*) FROM dept;

　　发现两张表中一共的数据量是18条，那么下面开始将这两张数据表执行一下多表查询。

SELECT COUNT(*) FROM emp,dept;

　　遗憾的是，发现最终的结果变为了56行记录，那么这56行的记录恰恰等于：雇员表的14条记录*部门表的4条记录，这样的操作在数据库中称为积，或者将其称为笛卡尔积。那么此时的情况如下。
　　

　　现在现在已经有积产生了，那么就应该想办法消除，则可以通过标的列来想办法、那么下面就必须依靠deptno来解决，毕竟emp表中的deptno字段的范围与dept表中的deptno定义的范围是一致的。
　　如果现在进行多表查询的数据表中有同名的字段出现，那么如果直接访问，就会出现”ORA-00918”，为了解决这样的同名的问题可以使用”表名称.字段”来加以区分。
范例：解决笛卡尔积的问题

SELECT *FROM emp,deptWHERE emp.deptno=dept.deptno;

　　此时就解决了之前积产生的问题，但是现在也有新的问题出现了，以上在访问表字段的时候使用的是”表.字段”，如果现在表的名称很长，这个时候就会发现访问起来非常的不方便，所以一般而言，此时可以使用别名的方式进行访问。
范例： 为数据表定义别名

SELECT *FROM emp e,dept dWHERE e.deptno=d.deptno;

　　发现在使用别名的时候，操作的形式会更加的简便，所以以后进行多表查询时，访问字段都会利用别名完成。
　　任何情况下，只要存在了多表查询的要求，那么数据表之中一定会永恒的存在消除笛卡尔积的字段。

范例：要求查询出每一位雇员的编号，姓名，职位，基本工资，部门名称，部门位置；
　· 确定要使用的数据表：
　　|- emp表：雇员的编号、姓名、职位、基本工资；
　　|- dept表：部门名称、部门位置；
　· 确定已知的关联字段：
　　|- 雇员和部门：emp.deptno = dept.deptno；
第一步：查询出每一位雇员的编号、姓名、职位、基本工资，此为单表查询

SELECT e.empno,e.ename,e.job,e.salFROM emp e;

第二步：查询部门信息，FROM子句之中增加dept表，同时由于增加了新的数据表，所以要在WHERE子句之中进行字段的关联，以消除笛卡尔积的显示，此处使用的是deptno字段完成。

SELECT e.empno,e.ename,e.job,e.sal,d.dname,d.loc FROM emp e,dept dWHERE e.deptno=d.deptno;

范例： 查询出每一位雇员的编号，姓名，基本工资，职位，雇佣日期，工资所在公司的工资等级。
　· 确定要使用的数据表：
　　|- emp表：雇员的编号、姓名、基本工资、职位、雇佣日期；
　　|- salgrade表：工资等级；
　· 确定已知的关联字段：
　　|- 雇员和工资等级：emp.sal BETWEEN salgrade.losal AND salgrade.hisal。
第一步：查询出每一位雇员的编号、姓名、基本工资、职位、雇佣日期　　

SELECT e.empno,e.ename,e.sal,e.jobFROM emp e;

第二步： 查询出工资登记，在FROM子句之中增加一个salgrade表，同时在WHERE子句之中应该增加一个消除笛卡尔积的操作条件(emp.sal BETWEEN salgrade.losal AND salgrade.hisal)

SELECT e.empno,e.ename,e.sal,e.job,e.hiredate,s.gradeFROM emp e,salgrade sWHERE e.sal BETWEEN s.losal AND s.hisal;

范例： 查询出每位雇员的编号，姓名，职位，工资，雇佣日期，部门名称，部门位置，工资等级
　· 确定要使用的数据表：
　　|- emp表：雇员的编号、姓名、职位、工资、雇佣日期；
　　|- dept表：部门名称、部门位置；
　　|- salgrade表：工资等级；
　· 确定已知的关联字段：这些条件一定要用AND连接
第一步： 查询出每位雇员的编号、姓名、职位、工资、雇佣日期

SELECT e.empno,e.ename,e.sal,e.job,e.hiredateFROM emp e;

第二步： 增加dept表的查询，同时增加一个消除笛卡尔积的操作条件

SELECT  e.empno,e.ename,e.sal,e.job,e.hiredate,d.locFROM emp e, dept dWHERE e.deptno=d.deptno;

第三步： 增加salgrade表

SELECT  e.empno,e.ename,e.sal,e.job,e.hiredate,d.loc,s.gradeFROM emp e, dept d,salgrade sWHERE e.deptno=d.deptno AND e.sal BETWEEN s.losal AND s.hisal;

　　所有的查询都不可能一次性的出现结果，都必须采用分步的方式归类完成。
　　

2、表的连接

对于数据表的链接，在SQL语法之中明确规定了两种类型：
　· 内连接：在之前所见到的所有的操作，都采用了一个等值的判断条件消除笛卡尔积，这样的链接的方式就称为等值链接，专业叫内链接，而等值链接的最大特点，就是把多张表中满足关系的数据都显示，不满足的数据不显示；
　· 外连接：左(外)连接、右(外)连接、全(外)链接。

但是为了更好的发现问题，下面首先向emp表中增加一条没有部门的雇员信息：

INSERT INTO emp(empno,ename,job,hiredate,sal,comm,mgr)         VALUES(8866,'WUSIR','CLERK',SYSDATE,100,NULL,7369);

　　下面就可以针对外连接进行分析，如果想要实现外连接，在Oracle中提供了一种非常简便的操作符号”(+)”进行操作，此操作分为两种情况：
　　· 左外连接： WHERE 字段 = 字段(+);
　　· 右外连接：WHERE 字段(+) = 字段；

范例： 操作左外连接，把左边表的数据显示完整

SELECT * FROM emp e,dept dWHERE e.deptno=d.deptno;SELECT * FROM emp e,dept dWHERE e.deptno=d.deptno(+);

范例： 操作右外连接，把右边表的数据显示完整

SELECT * FROM emp e,dept dWHERE e.deptno(+)=d.deptno;

　　使用外连接的时候只有一种情况，当发现需要的数据没有全部显示出来的时候考虑使用外连接。

范例： 要求显示每一位雇员的编号、姓名、职位、领导姓名、领导职位。
　　在emp表中现在只有mgr字段没有使用到了，此字段表示的是一个雇员对应的领导编号。

　　所以，现在发现，如果想要同时查询出雇员和领导的信息，就需要将数据表进行自己和自己关联，这种方式称为自身关联，于是下面继续按照之前的方式来进行操作的分析：
　· 确定所需要的数据表：
　　|- emp表：雇员的编号、姓名、职位；
　　|- emp（领导）表：领导的姓名、领导职位；
　· 确定已知的关联字段：
　　|- 雇员和领导：emp.mgr = memp.empno；
第一步：　查询雇员的编号、姓名、职位

SELECT e.empno,e.ename,e.jobFROM emp e;

第二步：　找出雇员的领导，增加一个关联条件，消除笛卡尔积

SELECT e.empno,e.ename,e.job,m.ename,m.jobFROM emp e,emp mWHERE e.mgr=m.empno;

第三步：　发现 7839 的雇员没有领导，所以以上的等值条件无法满足，那么此时需要它全部显示就必须采用外连接。

SELECT e.empno,e.ename,e.job,m.ename,m.jobFROM emp e,emp mWHERE e.mgr=m.empno(+);

解决两个问题：
问题1： 当你拿到一个新的数据库的某一张表时，你如果想知道这个表的数据是什么，你的第一反应是执行什么命令？
　　立刻执行查询，观察数据是什么：

SELECT * FROM 表名称;

　　这个时候发现根本就无法正常进行浏览，所以这些 做法都是菜鸟的做法。所以，一般老鸟的做法，是首先查询一下数据表中有多少数据量，使用COUNT()函数完成，如果数据量少，则直接发出 “SELECT * FROM 表名称;” 的语句查看数据，如果数据量大，利用某些操作只看第一行函数或者前N行数据。
　　现在就可以解决之前遗留的一个问题了：在讲解 NOT IN 的时候强调过一点：如果范围之中包含了null，那么最终不会有任何结果返回。

SELECT * FROM emp WHERE empno NOT IN (null);

　　如果按照以上的方式empno不是null就表示所有的数据都满足，那么就意味着要查询全部了，但是原本的含义只是查询部分，所以这个时候就会出现一个语法的bug，为了避免这种bug，所以才增加了次限制。
问题2： 多表查询的性能问题
　　如果内连接或者是外连接，最终可以解决的只是显示笛卡尔积的问题，但是数据库的内部也会存在笛卡尔积，那么如果说现在要连接两张表，例如，表A有80W条数据，表B有200W条数据，那么这两张表连接之后的数据量是：160,000,000,000，所以多表查询应该尽量避免使用。

3、SQL:1999语法支持

　　在之前的所有多表查询操作之中，等值连接是各个数据库都支持的操作，但是针对于外连接操作，那么其他的数据库之中是无法使用“(+)”符号，所以在SQL的1999语法之中定义了多表连接查询的操作形式。

SELECT table1.colunm,table2.columnFROM table1[CROSS JOIN table2]|[NATURAL JOIN table2]|[JOIN table2 USING (column_name)]|[JOIN table2 ON(table1.column_name=table2.column_name)]|[LEFT|RIGHT|FULLOUTER JOIN table2 ON(table1.column_name=table2.column_name)];

　　针对于以上的完整语法进行拆分讲解。

1、 交叉连接，语法如下：

SELECT table1.column,table2.columnFROM table1 CROSS JOIN table2 ;

范例：

SELECT table1.column,table2.columnFROM table1 CROSS JOIN table2 ;

范例：验证交叉连接，产生笛卡尔积

SELECT * FROM emp CROSS JOIN dept;

2、自然连接、语法如下：

SELECT table1.column,table2.columnFROM table1 NATURAL JOIN table2 ;

范例：验证自然连接，就相当于使用了等值连接，并且直接找到关联字段（外键约束）

SELECT * FROM emp NATURAL JOIN dept;

3、 USING子句，语法如下：

SELECT table1.column,talble2.columnFROM table JOIN table2 USING(列名称);

范例： 验证USING子句，由用户自己设置一个关联字段，而且一般的关联字段都是重名的

SELECT * FROM emp JOIN dept USING(deptno);

4、ON子句，语法如下：

SELECT table1.colum,table2.columnFROM table1 JOIN table2 ON(table1.column_name=table2.column_name);

范例： 验证 ON 子句

SELECT * FROM emp e JOIN dept d ON (e.deptno=d.deptno);

5、外连接，语法如下：

SELECT table.column,table2.columnFROM [LEFT|RIGHT|FULL OUTER JOIN table2ON (table1.column_name=table2.colnumn_name)];

范例： 验证全外连接，查到16条数据

SELECT * FROM emp e FULL JOIN dept d ON (e.deptno=d.deptno);

　　只有SQL:1999 语法才可以让两张表之中不满足等值条件的数据全部显示出来。

4、查询结果连接

　　在数学计算上应该学过：交集、并集、差积、补集，那么针对于查询结果也是可以进行此类连接的，为此在SQL语法中提供了UNION、UNION ALL、 INTERSECT、MINUS四种操作符号，这四种操作符号的使用语法形式如下：

SELECT [DISTINCT]*|列[别名],列[别名],...FROM 数据表[别名],表名称[别名],表明称[别名],...[WHERE 条件(s)][ORDER BY 字段 [ASC|DESC],字段[ASC|DESC],...]    [UNION|UNION ALL|INTERSECT|MINUS]SELECT [DISTINCT]*|列[别名],列[别名],...FROM 数据表[别名],表名称[别名],表明称[别名],...[WHERE 条件(s)][ORDER BY 字段 [ASC|DESC],字段[ASC|DESC],...];

范例： 验证UNION操作，重复的数据没有显示，查到15行数据

SELECT * FROM emp WHERE deptno=10    UNIONSELECT * FROM emp;

范例：验证UNION ALL操作，重复的数据显示，查到18行数据

SELECT * FROM emp WHERE deptno=10     UNION ALLSELECT * FROM emp ;

范例： 验证INTERSECT操作，两个查询结果中的相同部分，查到3行数据

SELECT * FROM emp WHERE deptno=10    INTERSECT SELECT * FROM emp;

范例： 验证MINUS操作，差积查到12行数据

SELECT * FROM emp    MINUSSELECT * FROM emp WHERE deptno=10;

　　但是在使用以上连接操作的时候还有一点需要注意，在进行多个查询结果连接显示的时候，要求查询的返回列的顺序完全相同，例如：以下的代码就是错误的。

SELECT ename,job,hiredate FROM emp     UNIONSELECT empno,sal,comm FROM emp WHERE deptno=10 ;

　　显示列必须相同，否则无法连接。

5、多表查询练习

1、列出所有员工的编号、姓名及其直接上级的编号姓名、显示的结果按领导的年工资降序排列。
　· 确定要使用的数据表：
　　|- emp表：编号、姓名；
　　|- emp表：上级的编号、姓名、年工资（不显示，作为排序条件）；
　· 确定要使用的数据表：
　　|- 雇员和领导：emp.mgr = memp.empno；
第一步： 找出员工的编号、姓名

SELECT e.empno,e.enameFROM emp e ;

第二步： 增加自身关联

SELECT e.empno,e.ename,m.empno,m.enameFROM emp e , emp mWHERE e.mgr=m.empno ORDER BY m.sal*12 DESC ;

第三步：缺少KING的信息，因为等值条件不满足，使用外连接

SELECT e.empno,e.ename,p.empno,p.sal*12 coFROM emp e,emp p WHERE e.mgr=p.empno(+)ORDER BY co DESC;

2、列出在部门”SALES”(销售部)工作的员工姓名、基本工资、部门名称，假定不知道销售的部门编号。
· 确定要使用的数据表：
　　|- emp表：员工姓名、基本工资、雇佣日期；
　　|- dept表：部门名称；
　· 确定已知的关联条件：
　　|- 雇员和领导：emp.mgr = memp.empno；
第一步：将emp表和dept表进行多表查询

SELECT e.ename,e.sal,e.hiredate,d.dnameFROM emp e,dept dWHERE e.deptno=d.deptno ;

第二步：找到销售部的雇员信息，增加一个条件，与之前的条件使用AND连接

SELECT e.ename,e.sal,d.dnameFROM emp e,dept dWHERE e.deptno=d.deptno AND d.dname='SALES';

3、列出所有员工的姓名、部门名称和工资。
· 确定要使用的数据表：
　　|- emp表：员工的姓名、工资；
　　|- dept表：部门名称；
　· 确定已知的关联条件：
　　|- 雇员和部门：emp.deptno = dept.deptno；
第一步：将emp表和dept表进行多表查询

SELECT e.ename,d.dname,e.salFROM emp e,dept dWHERE e.deptno=d.deptno;

第二步：找到销售部的雇员信息，增加一个条件，与之前的条件使用AND连接

SELECT e.ename,d.dname,e.salFROM emp e,dept dWHERE e.deptno=d.deptno;

4、列出所有员工的工资、所在部门的名称，按年薪从低到高排序。
· 确定要使用的数据表：
　　|- emp表：工资；
　　|- dept表：部门名称；
　· 确定已知的关联条件：
　　|- 雇员和部门：emp.deptno = dept.deptno；

SELECT d.dname,(e.sal+NVL(e.comm,0))*12 incomeFROM emp e,dept dWHERE e.deptno=d.deptno ORDER BY income ;

5、列出某个员工的上级主管及所在的部门名称，并要求出这些主管中的薪水超过 3000
· 确定要使用的数据表：
　　|- emp表：通过此张表的mgr字段才可以确定领导编号；
　　|- emp表：找到主关的信息，同时可以找到主管的部门编号、薪水；
　　|- dept表：找到部门名称；
　· 确定已知的关联条件：
　　|- 雇员和领导：emp.mgr = memp.empno；
　　|- 雇员和领导：emp.mgr = memp.empno；
第一步：查询出所有雇员的领导，最终显示的是领导信息，要求领导薪水超过3000

SELECT DISTINCT m.ename,m.salFROM emp e,emp mWHERE e.mgr=m.empno AND m.sal>3000 ;

第二步：找到部门名称

SELECT DISTINCT m.ename,m.sal,d.dnameFROM emp e,emp m,dept dWHERE e.mgr=m.empno AND m.sal>3000 AND m.deptno=d.deptno ;

　　多表查询关键是一个解决问题的思路，以后的这些思路应该在脑子中形成。