1. 子查询概念
子查询是指嵌套在其他语句（SELECT、INSERT、UPDATE、DELETE等）中的SELECT语句；子查询也称为内查询（inner query）或者嵌套查询（nested query）；子查询必须位于括号之中。
SQL中的子查询可以分为以下三种类型：
标量子查询（Scalar Subquery）：返回单个值（一行一列）的子查询。行子查询（Row Subquery）：返回单行结果（一行多列）的子查询，标量子查询是行子查询的一个特例。表子查询（Table Subquery）：返回一个虚拟表（多行多列）的子查询，行子查询是表子查询的一个特例。
2. 标量子查询
标量子查询的结果就像一个常量一样，可以用于SELECT、WHERE、GROUP BY、HAVING以及ORDER BY等子句中。以下示例在SELECT列表中使用标量子查询计算员工的月薪与平均月薪的差值：
SELECT emp_name, salary,salary - (SELECT AVG(salary) FROM employee) AS salary_diffFROM employeeWHERE emp_id <= 6;
3. 行子查询
行子查询可以当作一个一行多列的临时表使用。以下语句查找所有与“卧虎 ”在同一个部门并且职位相同的员工：
-- Oracle、MySQL 以及 PostgreSQL 实现SELECT emp_name, dept_id, job_idFROM employeeWHERE (dept_id, job_id) = (SELECT dept_id, job_id FROM employee WHERE emp_name = '卧虎')AND emp_name != '卧虎';
子查询返回了 “卧虎”所在的部门编号和职位编号，这两个值构成了一行数据；然后外部查询的WHERE条件使用该数据进行过滤，AND操作符用于排除“卧虎”自己。
行子查询的实际使用较少，SQL Server中不支持行子查询。
4. 表子查询
当子查询返回的结果包含多行数据时，称为表子查询。表子查询通常用于查询条件或者FROM子句中。
对于这种可能返回多行数据的表子查询，可以使用IN和NOT IN运算符进行判断。以上示例可以使用 IN 运算符改写如下：
SELECT emp_nameFROM employeeWHERE job_id IN (SELECT job_id FROM employee WHERE dept_id = 3);
5. ALL、ANY/SOME 运算符
ALL运算符与比较运算符（=、!=、<、<=、>、>=）结合表示等于、不等于、小于、小于等于、大于或者大于等于子查询结果中的所有值。
ANY/SOME运算符与比较运算符（=、!=、<、<=、>、>=）结合表示等于、不等于、小于、小于等于、大于或者大于等于子查询结果中的任意值。
SELECT emp_nameFROM employeeWHERE job_id = ANY (SELECT job_id FROM employee WHERE dept_id = 3);
6. EXISTS 操作符
EXISTS操作符用于判断子查询结果的存在性。如果子查询存在任何结果，EXISTS返回真；否则，返回假。
以下语句查找存在女性员工的部门：
SELECT d.dept_nameFROM department dWHERE EXISTS ( SELECT 1FROM employee eWHERE e.sex = '女'AND e.dept_id = d.dept_id)ORDER BY dept_name;
EXISTS之后是一个关联子查询，先执行外查询找到 d.dept_id；然后依次将 d.dept_id 传递给子查询，判断该部门是否存在女性员工；子查询一旦找到任何数据立即返回结果。EXISTS只判断结果的存在性，因此子查询的SELECT列表中的内容无所谓，通常使用一个常量值。该查询的结果表明“研发部”和“财务部”存在女性员工。
EXISTS只要找到任何数据，立即终止子查询的执行，因此可以提高查询的性能。
另外，NOT EXISTS执行相反的操作。如果想要查找不存在女性员工的部门，可以将上例中的EXISTS替换成NOT EXISTS。
现在，我们知道[NOT] EXISTS和[NOT] IN都可以用于判断子查询返回的结果。但是它们之间存在一个重要的区别：
[NOT] EXISTS只检查存在性，[NOT] IN需要比较实际的值是否相等。因此，当子查询的结果包含NULL值时，EXISTS仍然返回结果，NOT EXISTS不返回结果；但是此时IN和NOT IN都不会返回结果，因为 (X = NULL) 和NOT (X = NULL)的结果都是未知。
通常来说，[NOT] EXISTS的性能比[NOT] IN更好，尽量使用[NOT] EXISTS。
MySQL 学习笔记（16）— 子查询（单行单列 一行多列 多行多列 ALL ANY SOME 运算符 EXISTS 操作符）
