子查询

子查询（Subquery） 是嵌套在另一个查询中的 SQL 查询。它的目的是提供一个中间结果，用于外部查询的某一部分，比如 SELECT、FROM、WHERE 或 HAVING 子句中。子查询可以返回单个值、单列值、单行记录或多行记录，通常分为 标量子查询、列子查询、行子查询 和 表子查询。

1. 子查询的分类

1.1. 标量子查询

标量子查询返回单个值，可以用在 SELECT、WHERE 或 HAVING 子句中。
如果子查询返回多行或多列，通常会出错。

SELECT name
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

此查询返回薪资最高的员工的姓名。子查询 (SELECT MAX(salary) FROM employees) 计算出最大薪资，然后外部查询返回该薪资的员工姓名。

1.2. 列子查询

列子查询返回一列值，可以用在 WHERE 子句中与某一列进行比较。

SELECT name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

该查询返回位于 “New York” 地点的部门的所有员工的姓名。子查询返回部门ID，然后外部查询在 WHERE 子句中使用这些部门ID。

1.3. 行子查询

行子查询返回一行值，可以与外部查询中的多列进行比较。

SELECT name, salary
FROM employees
WHERE (department_id, job_id) = (SELECT department_id, job_id FROM employees WHERE name = 'John');

这个查询返回与 ‘John’ 所在部门和职位相同的所有员工的姓名和薪资。

1.4. 表子查询

表子查询返回多个行和列，通常用于 FROM 子句中。

SELECT emp_name, salary
FROM (SELECT emp_name, salary FROM employees WHERE department_id = 10) AS dept_10_employees;

这里的子查询从 employees 表中选择所有在部门10的员工，并且外部查询从子查询结果中提取这些员工的姓名和薪资。

2. 子查询在SQL中的不同位置

2.1. WHERE 子句

在 WHERE 子句中使用子查询：子查询常常用于限制外部查询的返回结果。例如，查找某个表中满足某些条件的数据。

SELECT name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE location = 'Chicago');

这里，子查询查找部门ID为 ‘Chicago’ 的部门，然后外部查询返回属于该部门的员工的姓名。

2.2. FROM 子句

在 FROM子句中使用子查询：将子查询的结果集作为一个临时表，用于外部查询。

SELECT avg_salary
FROM (SELECT salary FROM employees WHERE department_id = 10) AS dept_10_salaries;

在这个例子中，子查询返回部门10的所有薪资，外部查询计算这些薪资的平均值。

2.3. SELECT 子句

在SELECT子句中使用子查询：子查询的结果可以作为外部查询的列之一。

SELECT name,
       (SELECT MAX(salary) FROM employees WHERE department_id = 10) AS max_salary
FROM employees
WHERE department_id = 10;

该查询返回部门10中每位员工的姓名和该部门的最大薪资。

3. 子查询的类型

3.1. 相关子查询

相关子查询依赖于外部查询的列值。它在每一行的外部查询中都会执行一次，因此效率较低。

SELECT name
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

这里的子查询是相关的，因为它使用了外部查询中的 e.department_id 列。

3.2. 非相关子查询

非相关子查询与外部查询无关，子查询只执行一次，然后将结果用于外部查询。

SELECT name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

这里的子查询是非相关的，因为它不依赖于外部查询的任何列。