0
PostgreSQL 子查询（Subquery）详解
                           
天天向上
发布： 2025-03-12 23:48:28
原创
624 人浏览过

子查询（Subquery）是嵌套在另一个查询（如 SELECT、INSERT、UPDATE 或 DELETE）中的查询。子查询可以在数据库中充当一个临时的表，提供查询的数据。PostgreSQL 支持多种类型的子查询，包括标量子查询、列子查询、行子查询和连接子查询等。

1. 子查询的基本用法

子查询通常用于以下几种情形：

  • 作为查询条件：子查询可以作为 WHERE、HAVING 或 FROM 子句的条件，用于筛选数据。
  • 作为临时表：子查询可以作为一个虚拟表，在 FROM 子句中使用。

2. 子查询的类型

2.1 标量子查询（Scalar Subquery）

标量子查询返回一个单一值，通常在查询的 SELECT、WHERE 或 HAVING 子句中使用。

示例：

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

解释：这个查询选择所有工资高于员工平均工资的员工。子查询 (SELECT AVG(salary) FROM employees) 计算员工的平均工资，标量子查询的结果是一个单一值，用来与外部查询的工资进行比较。

2.2 列子查询（Column Subquery）

列子查询返回多个值，但只包含一个列。它通常用于 IN 或 NOT IN 子句中。

示例：

SELECT name, department
FROM employees
WHERE department IN (
    SELECT department
    FROM employees
    WHERE salary > 50000
);

解释：该查询查找所有工资高于 50000 的员工所在部门中的其他员工。子查询返回一个部门列表，外部查询通过 IN 子句使用该列表来筛选员工。

2.3 行子查询（Row Subquery）

行子查询返回多列和多行，通常用于比较多个列的值。它通常与 IN、ANY 或 ALL 一起使用。

示例：

SELECT name, department, salary
FROM employees
WHERE (department, salary) IN (
    SELECT department, MAX(salary)
    FROM employees
    GROUP BY department
);

解释：此查询查找每个部门中工资最高的员工。子查询返回每个部门的最大工资，外部查询根据部门和工资匹配员工信息。

2.4 相关子查询（Correlated Subquery）

相关子查询与外部查询的每一行相关联。它通过外部查询的列与子查询的列进行比较，执行时为每一行计算子查询。

示例：

SELECT e1.name, e1.salary
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e1.department = e2.department
);

解释：此查询查找每个部门中工资高于该部门平均工资的员工。子查询依赖于外部查询的 department 列，因此它是一个相关子查询。

3. 子查询的使用场景

3.1 子查询作为 SELECT 的一部分

子查询可以作为外部查询的列返回值之一。

示例：

SELECT name, (
    SELECT department
    FROM employees
    WHERE employees.id = employees_department.id
) AS department
FROM employees_department;

解释：此查询从 employees_department 表中选择员工的名字，并且为每个员工返回其所在的部门。

3.2 子查询作为 FROM 子句的一部分

子查询可以作为 FROM 子句中的表，通常用来创建临时表。

示例：

SELECT sub.department, sub.avg_salary
FROM (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS sub;

解释：该查询首先通过子查询计算每个部门的平均工资，然后在外部查询中获取这些结果。

3.3 子查询作为 WHERE 子句的一部分

子查询常常与 WHERE 子句配合使用，用来筛选数据。

示例：

SELECT name, salary
FROM employees
WHERE department = 'HR'
  AND salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department = 'HR'
);

解释：查询找出 HR 部门中工资高于部门平均工资的员工。

3.4 子查询作为 HAVING 子句的一部分

子查询也可以在 HAVING 子句中使用，以筛选分组后的数据。

示例：

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > (
    SELECT AVG(employee_count)
    FROM (
        SELECT department, COUNT(*) AS employee_count
        FROM employees
        GROUP BY department
    ) AS dept_counts
);

解释：该查询返回员工数多于所有部门平均员工数的部门。

4. 子查询性能优化

子查询在执行时可能需要多次扫描表，特别是相关子查询和大型数据集时，可能会影响查询性能。以下是一些优化子查询的方法：

4.1 使用连接代替子查询

对于某些子查询，尤其是非相关子查询，可以通过连接（JOIN）来替代，通常能提高性能。

示例：

SELECT e1.name, e1.salary
FROM employees e1
JOIN (
    SELECT department, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department
) e2
ON e1.department = e2.department
WHERE e1.salary = e2.max_salary;

解释：这个查询使用连接代替子查询，通常比直接使用子查询执行得更快。

4.2 使用 EXISTS 代替 IN

在处理具有较大集合的数据时，使用 EXISTS 替代 IN 子查询可能会更高效。

示例：

SELECT name
FROM employees e1
WHERE EXISTS (
    SELECT 1
    FROM employees e2
    WHERE e1.department = e2.department
      AND e2.salary > 50000
);

解释：EXISTS 子查询在找到第一个符合条件的记录时立即停止，这比 IN 子查询（会将所有匹配值加载到内存中）通常更高效。

5. 总结

PostgreSQL 中的子查询提供了强大的查询功能，能让你在查询中动态计算、筛选和聚合数据。通过适当地选择不同类型的子查询（如标量子查询、列子查询、行子查询等），你可以灵活地处理各种查询需求。同时，理解并应用优化技术，可以显著提升查询性能。

更多详细内容请关注其他相关文章！

发表回复 0

Your email address will not be published. Required fields are marked *