子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询

1.from后面的子查询

举例：查询员工的姓名和他上级的姓名

SELECT E.*, E2.EMPNO, E2.ENAME MNAME, E2.JOB, E2.SAL, E2.DEPTNO
 FROM EMP E, EMP E2
 WHERE E.MGR = E2.EMPNO(+);

所谓的from后面的子查询，无法就像将上面代码当作一张 “表” ，在外层在套一层select语句，表示从后面语句的结果集里进行字段筛选。

SELECT ENAME, MNAME
 FROM 
 (SELECT E.*, D.EMPNO, D.ENAME MNAME, D.JOB, D.SAL, D.DEPTNO
 FROM EMP E, EMP D
 WHERE E.MGR = D.EMPNO(+));

2.where后面的子查询

2.1 单行子查询

单行子查询：子查询的sql语句只查出一条记录

举例：查询工资大于10号部门平均工资的员工信息
首先查询10号部门的员工平均工资

SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 10;

也就是说我要查询的数据，其工资要大于我上面查的值。那我们可以这么写：

SELECT * FROM EMP WHERE SAL > (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 10);

2.2 多行子查询

多行子查询：子查询的sql语句查出若干条记录。
一般用于：IN,NOT IN,EXISTS,NOT EXISTS,ALL,ANY

举例：查询工资比20号部门所有人工资都高的员工信息
1)用单行子查询实现
思路：现在查询20部门最高的工资

SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 20;

然后将上面语句做为条件写入到where条件表达式种

 SELECT *
 FROM EMP
 WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 20);

2)用多行子查询实现：>all
思路：首先查询20部门的工资，将其视为一个集合

SELECT SAL FROM EMP WHERE DEPTNO = 20;

将上面查询的集合放入where条件中，用 >all (等价于大于最大的)

SELECT * FROM EMP WHERE SAL > ALL (SELECT SAL FROM EMP WHERE DEPTNO = 20);

3.having后面的子查询

举例：查询各个部门的部门编号和员工人数，要求部门的平均工资大于30号部门的平均工资
首先我们需要查询出30部门的平局工资

SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 30;

然后将其做为条件利用having进行过滤

SELECT DEPTNO, COUNT(*)
 FROM EMP
 GROUP BY DEPTNO
HAVING AVG(SAL) > (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 30);

4.select 和from 之间的子查询

注意：select 和from之间的子查询，子查询的结果只能是单行单列

SELECT EMPNO, ENAME, (SELECT 1 FROM DUAL) FROM EMP;