MySQL / 系统课时
标量子查询
标量子查询
标量子查询只返回一行一列,因此可以像普通值一样参与比较、计算或字段表达式。本课通过员工薪资查询理解它的写法、执行结果,以及返回多行时为什么会报错。
学习目标
- 能判断一个子查询是否满足“一行一列”的标量要求
- 能在
WHERE和SELECT中正确使用标量子查询 - 能识别并避免子查询返回多行导致的错误
标量子查询的核心规则
标量子查询是被括号包围的 SELECT,其结果必须最多只有一行一列。返回一个值时,这个值可以直接参与 =、>、< 等比较,也可以进入算术表达式;没有返回行时,结果按 NULL 处理。
例如,要找出薪资高于全体员工平均薪资的员工,可以先由内层查询计算平均值,再让外层查询逐行比较。
sql
CREATE TEMPORARY TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
salary DECIMAL(10,2) NOT NULL
);
INSERT INTO employees (id, name, salary) VALUES
(1, 'Alice', 6000.00),
(2, 'Bob', 8000.00),
(3, 'Carol', 10000.00);
SELECT id, name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY id;预期结果
text
+----+-------+----------+
| id | name | salary |
+----+-------+----------+
| 3 | Carol | 10000.00 |
+----+-------+----------+内层 AVG(salary) 只返回一个聚合值 8000.00,因此整个条件等价于逐行判断 salary > 8000.00。
在字段表达式中使用
标量子查询不只可以出现在筛选条件中,也可以放进 SELECT 列表。下面把每位员工的薪资与平均薪资同时输出,并计算差额。
sql
SELECT
name,
salary,
(SELECT AVG(salary) FROM employees) AS avg_salary,
salary - (SELECT AVG(salary) FROM employees) AS salary_gap
FROM employees
ORDER BY id;预期结果
text
+-------+----------+------------+------------+
| name | salary | avg_salary | salary_gap |
+-------+----------+------------+------------+
| Alice | 6000.00 | 8000.0000 | -2000.0000 |
| Bob | 8000.00 | 8000.0000 | 0.0000 |
| Carol | 10000.00 | 8000.0000 | 2000.0000 |
+-------+----------+------------+------------+这里的子查询与外层当前行无关,属于非相关子查询。实际执行时优化器可能对它进行一次计算并复用结果,不应简单理解为文本出现几次就一定执行几次。
返回多行时会发生什么
普通比较运算符需要右侧是单个值。如果子查询返回两行或更多行,就不再是标量子查询,MySQL 会报错。
sql
SELECT name
FROM employees
WHERE salary = (
SELECT salary
FROM employees
WHERE salary >= 8000.00
);预期输出
text
ERROR 1242 (21000): Subquery returns more than 1 row此时内层查询返回 8000.00 和 10000.00 两个值,不能直接放在 = 右侧。若业务语义本来就是“匹配多个可能值”,应改用 IN 等适合多值结果的运算符,而不是通过随意添加 LIMIT 1 掩盖数据关系。
NULL 与边界行为
如果标量子查询一行都没有返回,其结果为 NULL。与 NULL 使用普通比较运算符通常得到未知结果,因此不会通过 WHERE 条件。
sql
SELECT name
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE id = 999
);预期结果
text
Empty set设计标量子查询时,先确认它在数据约束下确实只会产生一个值。聚合函数、主键或唯一键条件通常容易满足这一要求;如果结果天然可能有多行,就应该选择列子查询对应的多值比较方式。
按自己的节奏,确认真正理解。