MySQL 学习笔记（16）— 子查询（单行单列、一行多列、多行多列、 ALL、ANY、SOME 运算符、EXISTS 操作符）

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_diff
  FROM employee
 WHERE emp_id <= 6;

3. 行子查询

行子查询可以当作一个一行多列的临时表使用。以下语句查找所有与“卧虎 ”在同一个部门并且职位相同的员工：

-- Oracle、MySQL 以及 PostgreSQL 实现
SELECT emp_name, dept_id, job_id
  FROM employee
 WHERE (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_name
  FROM employee
 WHERE job_id IN (SELECT job_id FROM employee WHERE dept_id = 3);

5. ALL、ANY/SOME 运算符

ALL 运算符与比较运算符（=、!=、<、<=、>、>=）结合表示等于、不等于、小于、小于等于、大于或者大于等于子查询结果中的所有值。

ANY/SOME 运算符与比较运算符（=、!=、<、<=、>、>=）结合表示等于、不等于、小于、小于等于、大于或者大于等于子查询结果中的任意值。

SELECT emp_name
  FROM employee
 WHERE job_id = ANY (SELECT job_id FROM employee WHERE dept_id = 3);

6. EXISTS 操作符

EXISTS 操作符用于判断子查询结果的存在性。如果子查询存在任何结果， EXISTS 返回真；否则，返回假。

以下语句查找存在女性员工的部门：

SELECT d.dept_name
  FROM department d
 WHERE EXISTS ( SELECT 1
                  FROM employee e
                 WHERE 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 。