嵌套子查询

sql
SELECT emp_name, salary -- Outer Query
FROM employee
WHERE salary > (
	SELECT AVG(salary) -- Inner Query
	FROM employee
);

标量子查询 Scalar Subquery

返回单个值（一行一列），可以像常量一样被用于SELECT、WHERE、GROUP BY、HAVING以及ORDER BY等子句中。

sql
-- 员工月薪与平均月薪之间的差值
SELECT emp_name AS "员工姓名", salary AS "月薪",
	salary - (SELECT AVG(salary) FROM employee) AS "差值"
FROM employee;

行子查询 Row Subquery

返回单个记录（一行多列），不常用。

sql
-- 查找所有与“孙乾”在同一个部门并且职位相同的员工
-- oracle mysql pg sqlite
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 != '孙乾'；

mss 不支持行子查询。

表子查询 Table Subquery

返回一个临时表（多行多列）。

where 中的子查询

因为子查询是表，所以不能使用比较运算符（=、!=、<、<=、>、>=等）。
可以使用 IN、NOT IN。

sql
-- 某个部门的全体员工
SELECT emp_name
FROM employee
WHERE job_id IN (
	SELECT job_id
	FROM employee
	WHERE dept_id = 1
);

ALL、ANY 子查询

ALL 运算符相当于多个 AND 运算符的组合，IN/ANY 运算符相当于多个 OR 运算符的组合。

ALL 运算符与比较运算符（=、!=、<、<=、>、>=）的组合分别表示等于、不等于、小于、小于或等于、大于、大于或等于子查询结果中的全部数据。

sql
-- 查找比“研发部”全体员工更晚入职的员工信息
-- oracle mysql mss pg
SELECT emp_name AS "员工姓名", hire_date AS "入职日期"
FROM employee
WHERE hire_date >ALL (
	SELECT e.hire_date
	FROM employee e
	JOIN department d ON (d.dept_id = e.dept_id)
	WHERE d.dept_name = '研发部'
);

sqlite 不支持 ALL。

sql
-- oracle
SELECT emp_name AS "员工姓名", hire_date AS "入职日期"
FROM employee
WHERE salary >ALL (10000, 15000, 20000);

ANY 运算符与比较运算符（=、!=、<、<=、>、>=）的组合分别表示等于、不等于、小于、小于或等于、大于、大于或等于子查询结果中的任何数据。

sql
-- 某个部门的全体员工
SELECT emp_name
FROM employee
WHERE job_id =ANY (
	SELECT job_id
	FROM employee
	WHERE dept_id = 1
);

sqlite 不支持 any。
SOME 关键字可以替代 ANY。

sql
-- oracle
SELECT emp_name
FROM employee
WHERE job_id !=ANY (1, 2, 2); -- 等价于 job_id != 1 OR job_id != 2

FROM 中的子查询

FROM子句中的子查询相当于一个临时表。

sql
-- 部门平均工资
SELECT d.dept_name AS "部门名称", ds.avg_salary AS "平均月薪"
FROM department d
LEFT JOIN (
	SELECT dept_id, AVG(salary) AS avg_salary
	FROM employee
	GROUP BY dept_id) ds
ON (d.dept_id = ds.dept_id);

子查询中使用排序通常没有意义。

sql
-- mysql pg sqlite
SELECT emp_name, salary
FROM employee
WHERE job_id IN (
	SELECT job_id
	FROM job
	WHERE max_salary >= 20000
	ORDER BY job_id
)
ORDER BY emp_name;

oracle mss 在子查询中使用排序会报错。

关联子查询 Correlated Subquery

子查询使用外部查询中的字段。

对于外部查询返回的每条记录，关联子查询都会执行一次（数据库可能会对此进行优化），非关联子查询只会独立执行一次。

sql
-- 每个部门的平均月薪
SELECT d.dept_name AS "部门名称",
	(SELECT AVG(salary) AS avg_aslary
	FROM employee
	WHERE dept_id = d.dept_id) AS "平均月薪"
FROM department d
ORDER BY d.dept_id;

-- 每个部门中最早入职的员工
SELECT d.dept_name AS "部门名称", o.emp_name AS "员工姓名", o.hire_date AS "入职日期"
FROM employee o
JOIN department d ON (d.dept_id = o.dept_id)
WHERE o.hire_date = (SELECT MIN(i.hire_date)
					FROM employee i
					WHERE i.dept_id = o.dept_id);

横向子查询 Lateral Subquery

关联子查询可以使用外部查询中的字段，但不能使用同一级别的其他查询或者表中的字段。以下是一个错误的示例：

sql
-- 每个部门的最大月薪
SELECT d.dept_name, t.max_salary
FROM department d
JOIN (SELECT MAX(e.salary) AS max_salary
	 FROM employee e
	 WHERE e.dept_id = d.dept_id) t;

横向子查询，允许派生表使用它所在的FROM子句中左侧的其他查询或者表：

sql
-- oracle mysql pg
SELECT d.dept_name "部门名称", t.max_salary AS "最高月薪"
FROM department d
CROSS JOIN
LATERAL (SELECT MAX(e.salary) AS max_salary
		FROM employee e
		WHERE e.dept_id = d.dept_id) t;

-- mss oracle
SELECT d.dept_name "部门名称", t.max_salary AS "最高月薪"
FROM department d
CROSS APPLY (SELECT MAX(e.salary) AS max_salary
		FROM employee e
		WHERE e.dept_id = d.dept_id) t;

除 CROSS APPLY 外，还有一个 OUTER APPLY，它是左外连接的一种变体，同样允许右侧的子查询使用 FROM 子句中左侧查询或者表中的字段。Microsoft SQL Server和Oracle提供了这两种查询语法。
sqlite 不支持横向子查询。

EXISTS 运算符

用于判断子查询结果的存在性。只要子查询返回了任何结果，就表示满足查询条件；如果子查询没有返回任何结果，就表示不满足查询条件。

sql
-- 拥有女性员工的部门
SELECT d.dept_name AS "部门名称"
FROM department d
WHERE EXISTS (SELECT 1
			 FROM employee e
			 WHERE e.sex = '女'
			 AND e.dept_id = d.dept_id)
ORDER BY dept_name;

[NOT] EXISTS运算符只检查结果的存在性，[NOT] IN运算符需要比较实际的值是否相等。

当子查询的结果中只有 NULL 值时，EXISTS 运算符仍然可以返回结果，NOT EXISTS 运算符则不返回结果。但是，此时 IN 和 NOT IN 运算符都不会返回结果，因为任何数据和空值进行比较的结果均未知。

sql
-- 不拥有女性员工的部门
SELECT d.dept_name
FROM department d
WHERE NOT EXISTS (SELECT NULL
			 FROM employee e
			 WHERE e.dept_id = d.dept_id
			 AND sex = '女');

NOT IN 的等价写法

sql
SELECT d.dept_name AS "部门名称"
FROM department d
WHERE d.dept_id NOT INT (3, 4, NULL);

-- 等价于
SELECT d.dept_name AS "部门名称"
FROM department d
WHERE d.dept_id != 3
AND d.dept_id != 4
AND d.dept_id != NULL;

参考

什么是 PostgreSQL 横向子查询？

目录