子查询：出现在其它语句内部的select语句，称为子查询或内查询，就是嵌套的查询结果集
子查询分类
按子查询出现的位置：
- select后面：仅仅支持标量子查询
- from后面：支持表子查询
- where或having后面：标量子查询、列子查询、行子查询
- exists后面（相关子查询）
按结果集的行列数不同：
- 标量子查询：结果集只有一行一列
- 列子查询：结果集只有一列多行
- 行子查询：结果集有一行多列，也可多行多列
- 表子查询：结果集一般为多行多列
特点：
- 子查询放在小括号内
- 子查询一般放在条件的右侧
- 标量子查询一般搭配单行操作符的使用（> < <= >= = <>）
- 列子查询一般搭配多行操作符的使用（in、any、all）
一、where后面子查询
1.标量子查询
案例1：谁的工资比Abel高？
首先需要查询出Abel的工资，查询结果为一个标量集，所以在where后面当一个常量值使用
SELECT last_name, salary FROM employees WHERE salary > ( SELECT salary FROM employees WHERE last_name = 'Abel')
案例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名、job_id和工资
首先需要查询出141号的job_id，然后再查询出143号的salary，然后再主查询中进行条件筛选
SELECT last_name, job_id, salary FROM employees WHERE job_id = ( SELECT job_id FROM employees WHERE employee_id = 141) AND salary >( SELECT salary FROM employees WHERE employee_id = 143)
案例3：返回公司工资最少的员工的last_name，job_id和salary
SELECT last_name, job_id, salary FROM employees WHERE salary =( SELECT min( salary ) FROM employees)
案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id, min( salary ) FROM employees GROUP BY department_id HAVING min( salary ) > ( SELECT MIN( salary ) FROM employees WHERE department_id = 50)
2.列子查询
案例1：返回location_id是1400或1700的部门中的所有员工姓名
首先查询出location_id为1400或1700的部门编号
SELECT last_name FROM employees WHERE department_id IN ( SELECT department_id FROM departments WHERE location_id IN ( 1400, 1700 ) )
案例2：返回其他工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id以及salary
首先查询出工种为IT_PROG的工资列表，由于是小于任一，也就是小于其中任意一个就行，所以使用any关键字进行筛选
SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary < ANY ( SELECT salary FROM employees WHERE job_id = 'IT_PROG' ) AND job_id <> 'IT_PROG';
3.行子查询
案例：查询员工编号最小并且工资最高的员工信息
先把最小编号和最高工资查询出来，是一个行信息，然后用于之后的筛选
SELECT * FROM employees WHERE ( employee_id, salary ) = ( SELECT min( employee_id ), max( salary ) FROM employees );
二、select后面子查询
案例1：查询每个部门的员工个数及其部门信息
SELECT d.*, ( SELECT count(*) FROM employees WHERE department_id = d.department_id ) FROM departments d;
案例2：查询员工号为102的部门名
SELECT ( SELECT department_name FROM departments d JOIN employees e ON d.department_id = e.department_id WHERE e.employee_id = 102 );
三、from后面子查询
案例：查询每个部门的平均工资的工资等级
首先查出每个部门的平均工资，相当于一张新的表，由于是中间数据新表，所以需要起别名，然后再使用sql99的非等值连接即可
SELECT avg, grade_level FROM ( SELECT avg( salary ) avg FROM employees GROUP BY department_id ) a JOIN job_grades j ON a.avg BETWEEN j.lowest_sal AND j.highest_sal;
四、exists后面（相关子查询）
exist可以看成一个函数，该函数判断给定结果集中是否有数据，如果有则返回1，否则返回0，就是一个判断结果集是否为空的函数
案例1：查询有员工的部门名
可以看成是双重for循环，首先遍历部门表，拿着部门id去筛选，判断员工表中是否有员工的部门id等于该id，一旦有返回的集合就不为空，该部门名选中，如果没有则返回空。
第二种方式使用in，首先查询出员工表中是否有部门表中的部门id，然后使用in判断部门表的部门id是否在该子查询中
# 方式一 SELECT department_name FROM departments d WHERE EXISTS ( SELECT * FROM employees WHERE department_id = d.department_id ) # 方式二 SELECT department_name FROM departments d WHERE department_id IN ( SELECT department_id FROM employees WHERE department_id = d.department_id)
案例2：查询没有女朋友的男神信息
方式一使用exist，拿着男表中的id去查询女表中，一旦查询出结果，那么则返回1，所以使用notexist，不存在即结果集为空则筛选对该男信息
方式二使用not in，查询出女表中的所有boyid，然后判断男表的id是否在其中
# 方式一 SELECT * FROM boys WHERE NOT EXISTS ( SELECT * FROM beauty WHERE boyfriend_id = boys.id ) # 方式二 SELECT * FROM boys WHERE id NOT IN ( SELECT boyfriend_id FROM beauty );
