SQL Academy · 课时

相关子查询

Q: 「相关子查询」这节课中我会学到什么？

了解依赖外层行的子查询 你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

了解依赖外层行的子查询

相关子查询 是 CoddyKit 上的免费 SQL Academy 课时。这是第 1 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 SQL Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Academy 课程共包含 4 节课。

什么是相关子查询？

相关子查询是引用外层（包含它的）查询中的某一列的子查询。普通子查询只运行一次并返回固定结果，而相关子查询会针对外层查询处理的每一行分别求值。

这使它非常适合逐行比较，但其开销也高于简单子查询。

普通子查询与相关子查询

关键区别在于：普通子查询不引用外层查询，可以独立运行。相关子查询依赖外层行——您可以看到外层表别名出现在子查询中。

在下面的示例中，内层 SELECT 引用了外层查询中的 e1.department_id，从而建立了关联。

-- Regular subquery (runs once)
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Correlated subquery (runs once per outer row)
SELECT name, salary, department_id
FROM employees e1
WHERE salary > (
  SELECT AVG(salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

设置示例表

让我们创建本课中会一直使用的两个表：employees 和 departments。这些表提供了贴近实际的数据，用于演示相关子查询在不同场景中的应用。

CREATE TABLE departments (
  id   INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE employees (
  id            INT PRIMARY KEY,
  name          VARCHAR(50),
  department_id INT REFERENCES departments(id),
  salary        NUMERIC(10,2),
  hire_date     DATE
);

INSERT INTO departments VALUES (1,'Engineering'),(2,'Sales'),(3,'HR');

INSERT INTO employees VALUES
  (1,'Alice',  1, 90000, '2020-03-01'),
  (2,'Bob',    1, 75000, '2021-06-15'),
  (3,'Carol',  2, 60000, '2019-01-10'),
  (4,'David',  2, 68000, '2022-09-01'),
  (5,'Eve',    3, 55000, '2020-07-20'),
  (6,'Frank',  1, 95000, '2018-11-05'),
  (7,'Grace',  3, 52000, '2023-02-28'),
  (8,'Henry',  2, 71000, '2021-04-12');

查找薪资高于所在部门平均值的员工

相关子查询的经典应用：找出每名薪资高于其所在部门平均薪资的员工。内层查询会针对外层查询中的每一名员工重新计算其部门的平均薪资。

SELECT e1.name, e1.department_id, e1.salary
FROM employees e1
WHERE e1.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
)
ORDER BY e1.department_id, e1.salary DESC;

在 SELECT 中使用相关子查询

相关子查询并不局限于 WHERE 子句——它们也可以出现在 SELECT 列表中，为每一行计算一个值。在这里，我们在一个查询中获取每名员工的薪资以及其所在部门的平均薪资。

SELECT
  e.name,
  e.salary,
  (
    SELECT ROUND(AVG(e2.salary), 2)
    FROM employees e2
    WHERE e2.department_id = e.department_id
  ) AS dept_avg_salary
FROM employees e
ORDER BY e.department_id, e.name;

查找每个部门薪资最高的员工

我们可以使用相关子查询，找出每个部门中薪资最高的员工。内层查询会找出当前行所属部门的最高薪资，外层查询则只保留与该薪资匹配的行。

SELECT e1.name, e1.department_id, e1.salary
FROM employees e1
WHERE e1.salary = (
  SELECT MAX(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
)
ORDER BY e1.department_id;

使用相关子查询配合 EXISTS

EXISTS 运算符经常与相关子查询搭配使用。如果内层查询至少生成一行，它就返回 TRUE。这里我们列出所有至少有一名员工在 2021 年之前入职的部门。

SELECT d.name AS department
FROM departments d
WHERE EXISTS (
  SELECT 1
  FROM employees e
  WHERE e.department_id = d.id
    AND e.hire_date < '2021-01-01'
);

使用相关子查询配合 NOT EXISTS

NOT EXISTS 的作用相反——当相关子查询找不到任何匹配行时，它返回 TRUE。这对于查找没有子记录的父记录很有用，例如没有员工的部门。

SELECT d.name AS department_without_employees
FROM departments d
WHERE NOT EXISTS (
  SELECT 1
  FROM employees e
  WHERE e.department_id = d.id
);

在 UPDATE 中使用相关子查询

相关子查询也可以用于 UPDATE 语句。下面的示例会添加一个 dept_avg 列，然后使用相关子查询为其填入每名员工所在部门的平均薪资。

ALTER TABLE employees ADD COLUMN dept_avg NUMERIC(10,2);

UPDATE employees e1
SET dept_avg = (
  SELECT ROUND(AVG(e2.salary), 2)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

SELECT name, salary, dept_avg FROM employees ORDER BY department_id, name;

在 DELETE 中使用相关子查询

您也可以在 DELETE 语句中使用相关子查询，根据相关表中的数据删除行。下面的查询会删除薪资低于所在部门平均薪资 60% 的员工——这是一种数据清理模式。

DELETE FROM employees e1
WHERE e1.salary < (
  SELECT AVG(e2.salary) * 0.60
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

SELECT name, salary, department_id FROM employees ORDER BY department_id;

性能提示：相关子查询与 JOIN

相关子查询会针对每一行外层数据运行一次，在大型表上可能会很慢。许多相关子查询都可以重写为使用派生表或 CTE 的 JOIN，以获得更好的性能。请理解这两种模式，并根据可读性和执行计划进行选择。

-- Correlated version (potentially slower)
SELECT e1.name, e1.salary
FROM employees e1
WHERE e1.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

-- Equivalent JOIN + derived table (often faster)
SELECT e.name, e.salary
FROM employees e
JOIN (
  SELECT department_id, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department_id
) dept_avg ON dept_avg.department_id = e.department_id
WHERE e.salary > dept_avg.avg_sal;

快速检查

检验您对相关子查询的理解。

回顾：相关子查询

在本课中，您了解到相关子查询会引用外层查询中的某一列，并针对外层的每一行重新求值。要点：

它们可以出现在 SELECT、WHERE、UPDATE 和 DELETE 中。
EXISTS / NOT EXISTS 天然适合与相关子查询搭配，用于检查相关行是否存在。
它们表达力强，但可能运行缓慢——当性能很重要时，可以考虑将其重写为 JOIN。
子查询中的外层表别名正是建立关联的关键。

常见问题解答

「相关子查询」课时是免费的吗？

是的 — 「相关子查询」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Academy 课程的其余内容，请升级到 CoddyKit PRO。 SQL Academy 课程共包含 4 节课。

「相关子查询」这节课中我会学到什么？

了解依赖外层行的子查询你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 SQL Academy 需要有经验吗？

无需任何先前经验。CoddyKit 上的 SQL Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 1 节课，共 4 节。

「相关子查询」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 SQL Academy 课中编写并运行代码吗？

能。每节 SQL Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 SQL Academy