PostgreSQL - 子查询

PostgreSQL - 子查询和 CTEs

子查询（subquery），也称为内部查询（inner query）或嵌套查询（nested query），是嵌入在另一个主查询（例如，在 WHERE、FROM 或 SELECT 子句中）内的 SELECT 查询。其目的是返回一个结果集，供主查询用于过滤、评估或检索更多数据。

子查询是 SQL 的基本组成部分，但对于复杂的逻辑，现代 PostgreSQL 提供了一种更具可读性和强大功能的替代方案：公共表表达式（Common Table Expressions，CTEs）。

带有 WHERE 子句的子查询

这是最常见的用例。子查询返回一个值或值的列表，用于比较。

语法指南

• 子查询必须括在括号 () 中。
• 与比较运算符（=、>、<）一起使用的子查询应返回单个值（标量子查询）。
• 与 IN 或 NOT IN 一起使用的子查询可以返回多个值的列表。
• 与 EXISTS 一起使用的子查询检查其结果集中是否存在任何行。

示例

考虑两个表：employees 和 departments。

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    salary NUMERIC(10, 2),
    department_id INT REFERENCES departments(id)
);

INSERT INTO departments (name) VALUES ('Sales'), ('Engineering'), ('HR');
INSERT INTO employees (name, salary, department_id) VALUES
('Alice', 90000, 2), ('Bob', 85000, 2), ('Charlie', 70000, 1), ('David', 45000, 3);

让我们使用子查询查找所有在 ‘Engineering’ 部门工作的员工。

SELECT name, salary
FROM employees
WHERE department_id = (SELECT id FROM departments WHERE name = 'Engineering');

这将产生以下结果：

  name  | salary
--------+---------
 Alice  | 90000.00
 Bob    | 85000.00
(2 rows)

最佳实践：虽然上述子查询有效，但 JOIN 通常更具可读性，并且性能可能更好：SELECT e.name, e.salary FROM employees e JOIN departments d ON e.department_id = d.id WHERE d.name = 'Engineering';

使用公共表表达式（CTEs）提高可读性

当查询变得复杂，包含多个嵌套子查询时，它们可能难以阅读和调试。公共表表达式（CTEs），使用 WITH 子句定义，允许您创建临时的、命名的结果集，您可以在主查询中引用它们。它们的作用类似于单次查询的临时表。

语法

WITH cte_name AS (
    -- 定义 CTE 的子查询
    SELECT ...
)
-- 使用 CTE 的主查询
SELECT ... FROM cte_name;

示例：使用 CTE 重写复杂查询

让我们查找平均薪资高于公司整体平均薪资的部门。

不使用 CTE，这可能会很混乱：

SELECT name from departments WHERE id IN (
  SELECT department_id
  FROM employees
  GROUP BY department_id
  HAVING AVG(salary) > (SELECT AVG(salary) FROM employees)
);

使用 CTE 使逻辑清晰得多：

WITH department_avg_salaries AS (
    SELECT department_id, AVG(salary) as avg_dept_salary
    FROM employees
    GROUP BY department_id
),
overall_avg_salary AS (
    SELECT AVG(salary) as avg_overall_salary FROM employees
)
SELECT d.name
FROM departments d
JOIN department_avg_salaries das ON d.id = das.department_id
WHERE das.avg_dept_salary > (SELECT avg_overall_salary FROM overall_avg_salary);

这个基于 CTE 的查询虽然更冗长，但结构更清晰、更具自解释性，并且更易于维护。

INSERT、UPDATE 和 DELETE 中的子查询

子查询对于数据修改语句也很有用。

INSERT 与子查询

您可以将查询结果插入到另一个表中。例如，归档高收入员工。

CREATE TABLE high_earners_archive (name VARCHAR(100), salary NUMERIC);

INSERT INTO high_earners_archive (name, salary)
SELECT name, salary
FROM employees
WHERE salary > 80000;

UPDATE 与子查询

为 ‘Sales’ 部门的所有员工涨薪 10%。

UPDATE employees
SET salary = salary * 1.10
WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales');

DELETE 与子查询

删除 ‘HR’ 部门的所有员工。

DELETE FROM employees
WHERE department_id = (SELECT id FROM departments WHERE name = 'HR');