（六）.多表查询，子查询课堂SQL

多表查询

出于数据库设计的一个考虑，根据业务模块和逻辑的考虑
–会拆分成不同的表来存储对应的数据，从而降低数据的冗余性
–提高数据维护的灵活性，但是在实际需求中又需要用到多张表的
–数据同时出现，对应产生了多表查询

---

笛卡尔积 (危险操作)

SELECT E.*,D.* FROM EMP E,DEPT D;
SELECT * FROM EMP;
SELECT * FROM DEPT;

多表查询

–内连接（等值连接，简单连接） INNER JOIN…ON
–查询员工以及所属部门的信息

SELECT E.*,D.* FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO;

SELECT E.*,D.* FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO;

–查询部门号为10的员工信息以及部门所在地，并且按照工资来排序

SELECT E.*,D.LOC FROM EMP E,DEPT D 
WHERE E.DEPTNO=D.DEPTNO  AND E.DEPTNO=10 ORDER BY E.SAL;

–外连接（左外连接，右外连接）
–是内连接的一个拓展，既返回符合连接条件的行
–又返回指定的不符合连接条件的行

–右外连接 RIGHT JOIN…ON（强制右边表的数据全部显示，即使不符合连接条件）

SELECT E.*,D.* FROM 
EMP E RIGHT JOIN DEPT D ON E.DEPTNO=D.DEPTNO;

–左外连接 LEFT JOIN…ON （强制左边表的数据全部显示，即使不符合连接条件）

SELECT E.*,D.* FROM 
EMP E LEFT JOIN DEPT D ON E.DEPTNO=D.DEPTNO;

–子查询
–把一个SELECT语句嵌入另一个SELECT语句
–被嵌入的语句叫做容器语句或者父查询
–可以看做两个连续查询的查询语句
–可以把子查询的结果理解成一张虚表
–子查询一定先执行（相关子查询除外）

–查询比SMITH工资高的员工

SELECT * FROM EMP WHERE SAL>
(SELECT SAL FROM EMP WHERE ENAME='SMITH');

–查询与SMITH在同一部门的员工

SELECT * FROM EMP WHERE DEPTNO = 
(SELECT DEPTNO FROM EMP WHERE ENAME='SMITH');

–查询部门位于NEW YORK的员工信息

SELECT * FROM EMP WHERE DEPTNO =
(SELECT DEPTNO FROM DEPT WHERE LOC='NEW YORK');

–多行子查询 IN,ANY,ALL

–查询每个部门薪水最高的员工

SELECT * FROM EMP WHERE SAL IN
(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

–查询20部门比30部门所有员工薪水低的员工

SELECT * FROM EMP WHERE DEPTNO = 20
AND SAL<ALL(SELECT SAL FROM EMP WHERE DEPTNO=30);

–查询20部门比30部门任意员工薪水低的员工

SELECT * FROM EMP WHERE DEPTNO = 20
AND SAL<ANY(SELECT SAL FROM EMP WHERE DEPTNO=30);

–相关子查询
–当子查询涉及到父查询数据时会先进行父查询
–取得数据传入子查询进行子查询
–最后比较，周而复始知道数据查询完毕

–查询每个部门工资比每个部门平均工资高的员工

SELECT E.* FROM EMP E WHERE E.SAL>
(SELECT AVG(SAL) FROM EMP P WHERE P.DEPTNO=E.DEPTNO);

–自连接
–一个表的字段取连接它本身的一个字段

–查询SMITH的顶头上司

SELECT * FROM EMP;

SELECT P.MGR  FROM EMP E,EMP P
WHERE E.MGR = P.EMPNO AND E.ENAME='SMITH';

–查询SMITH的工资等级

SELECT * FROM SALGRADE;

SELECT E.*,S.* FROM EMP E,SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL AND E.ENAME='SMITH';