标量子查询

什么是标量子查询？

标量子查询是一种特殊的子查询（嵌套在另一个查询中的 SELECT 语句），它有且仅有返回一行一列的数据。这个单个返回值可以被用在外部查询的各种表达式中，就像使用一个普通的单值一样。

定义标量子查询 = 返回单个值（一行一列）的子查询

标量子查询的基本语法

sql

SELECT column_name, (SELECT aggregate_function(column_name)
                     FROM table_name2
                     WHERE condition)
FROM table_name1;

工作原理

标量子查询的执行过程可以用下图表示：

使用场景

标量子查询在以下情况特别有用：

需要在 SELECT 子句中使用聚合函数的结果
基于条件过滤数据时需要动态计算比较值
在 UPDATE 语句中计算新值
在条件表达式中使用计算结果

实例详解

示例 1：查找每个州人口最多的城市的人口数

假设我们有两个表：states（州）和cities（城市）。

states 表结构： | 列名 | 数据类型 | 描述 | |------|----------|------| | name | text | 州名 | | area | numeric | 面积 |

cities 表结构： | 列名 | 数据类型 | 描述 | |------|----------|------| | name | text | 城市名 | | state | text | 所属州 | | pop | integer | 人口数量 |

查询：

sql

SELECT name,
       (SELECT max(pop)
        FROM cities
        WHERE cities.state = states.name) AS max_population
FROM states;

执行过程分析：

外部查询从states表中选择每个州
对于每个州，执行内部子查询
子查询找出该州所有城市中人口最多的数量
返回结果包含州名和该州最大城市人口数

示例输出： | name | max_population | |------|----------------| | California | 3792621 | | New York | 8175133 | | Texas | 2099451 | | Florida | 821784 |

示例 2：查询每个员工的工资与部门平均工资的差距

查询：

sql

SELECT
    employee_name,
    salary,
    (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e1.department) AS dept_avg,
    salary - (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e1.department) AS difference
FROM
    employees e1;

输出示例： | employee_name | salary | dept_avg | difference | |---------------|--------|----------|------------| | Alice | 50000 | 45000 | 5000 | | Bob | 40000 | 45000 | -5000 | | Charlie | 60000 | 55000 | 5000 | | David | 50000 | 55000 | -5000 |

注意事项

标量子查询必须严格返回一行一列的结果，否则会出现错误。

有几点需要特别注意：

行数限制：如果子查询返回多于一行结果，将会产生错误。
列数限制：如果子查询返回多于一列，也会产生错误。
空结果处理：如果子查询没有返回任何行，标量结果将被视为 NULL。
性能考虑：标量子查询会对每一行外部查询执行一次，可能导致性能问题。

与其他子查询的区别

子查询类型	返回值	使用位置
标量子查询	单个值（一行一列）	可用于大多数需要单个值的表达式中
行子查询	多列（一行多列）	可用于比较操作和 IN 子句中
表子查询	任意行列（多行多列）	FROM 子句、IN 子句、EXISTS 子句中

最佳实践

使用适当的索引提高相关子查询的性能
考虑使用 JOIN 代替相关子查询以提高某些查询的性能
始终确保子查询只返回一行一列
对可能返回多行的子查询使用聚合函数（如 MAX、MIN、AVG）

进阶：标量子查询的替代方案

有时，JOIN 操作可能比标量子查询更高效：

sql

-- 使用标量子查询
SELECT name,
       (SELECT max(pop)
        FROM cities
        WHERE cities.state = states.name) AS max_population
FROM states;

-- 使用JOIN和窗口函数的替代方案
SELECT DISTINCT ON (s.name)
    s.name,
    c.pop AS max_population
FROM
    states s
JOIN
    cities c ON c.state = s.name
ORDER BY
    s.name, c.pop DESC;

选择哪种方案应基于具体场景和性能测试结果。

总结

标量子查询是 SQL 中一种强大的功能，它允许您在需要单个值的任何地方执行复杂的计算。虽然它们提供了极大的灵活性，但也需要谨慎使用以避免性能问题。通过正确使用标量子查询，可以使复杂的数据分析任务变得更加简洁和可读。

标量子查询 ​

什么是标量子查询？ ​