📚 目录

  1. 概述
  2. 子查询的类型
  3. 子查询的语法
  4. 子查询的使用示例
  5. 子查询的性能优化
  6. 参考资料

🛠️ 概述

子查询（Subquery） 是嵌套在其他 SQL 语句中的查询。子查询可以用于 SELECT、INSERT、UPDATE 或 DELETE 操作的 WHERE、FROM 或 SELECT 子句中。子查询通常用于处理复杂的查询需求，可以帮助用户避免重复的操作或者简化复杂的查询逻辑。

在 PostgreSQL 中，子查询可以是标量子查询（返回单个值）、列子查询（返回多个列）、表子查询（返回多个行和列）等类型。

📝 子查询的类型

  1. 标量子查询： 返回单一的值。标量子查询通常用于比较运算符（如 =、>、< 等）中。 例如： SELECT employee_id, name FROM employees WHERE salary = (SELECT MAX(salary) FROM employees); 这个子查询返回 employees 表中的最高薪水，外部查询返回所有薪水等于最高薪水的员工。
  2. 列子查询： 返回一列数据的子查询，通常与 IN、ANY 或 ALL 等运算符一起使用。 例如： SELECT employee_id, name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York'); 这个子查询返回 New York 地点的所有部门 ID，外部查询返回所有属于这些部门的员工。
  3. 表子查询： 返回多行多列的结果集，通常与 FROM 子句一起使用。 例如： SELECT avg_salary, department_id FROM (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) AS avg_dept_salaries WHERE avg_salary > 50000; 这个子查询计算了每个部门的平均薪水，外部查询选择平均薪水大于 50000 的部门。

📝 子查询的语法

子查询可以放在 SQL 语句的多个位置：

  1. 在 SELECT 子句中： SELECT employee_id, (SELECT department_name FROM departments WHERE department_id = employees.department_id) AS department FROM employees;
  2. 在 FROM 子句中： SELECT avg_salary, department_id FROM (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) AS avg_dept_salaries;
  3. 在 WHERE 子句中： SELECT employee_id, name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
  4. 在 HAVING 子句中： SELECT department_id, COUNT(*) AS num_employees FROM employees GROUP BY department_id HAVING COUNT(*) > (SELECT AVG(num_employees) FROM (SELECT department_id, COUNT(*) AS num_employees FROM employees GROUP BY department_id) AS dept_count);

📝 子查询的使用示例

示例 1：标量子查询

找出薪水高于所有销售部门员工薪水的员工：

SELECT employee_id, name, salary
FROM employees
WHERE salary > (SELECT MAX(salary) FROM employees WHERE department = 'Sales');

这个查询返回薪水高于销售部门所有员工薪水的员工。

示例 2：列子查询

找出属于特定部门的所有员工：

SELECT employee_id, name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'IT');

这个查询返回所有属于 IT 部门的员工。

示例 3：表子查询

计算每个部门的平均薪水并找出平均薪水大于 60000 的部门：

SELECT department_id, avg_salary
FROM (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) AS dept_avg
WHERE avg_salary > 60000;

此查询计算了每个部门的平均薪水，并返回那些平均薪水大于 60000 的部门。

🛠️ 子查询的性能优化

尽管子查询非常强大，但它们有时会影响查询性能，特别是当子查询处理大量数据时。为了提高子查询的性能，可以采用以下方法：

  1. 使用联接（JOIN）替代子查询：在许多情况下，可以通过将子查询重写为联接来提高查询性能。例如，使用 INNER JOIN 来替代 IN 或 EXISTS 子查询。 SELECT e.employee_id, e.name FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE d.department_name = 'IT';
  2. 使用物化视图（Materialized Views）：对于重复执行的复杂子查询，可以将其存储为物化视图，从而避免每次都重新执行子查询。
  3. 索引优化：确保子查询中涉及的表有合适的索引，以提高查询的速度。
  4. 避免不必要的子查询：简化查询逻辑，避免在查询中使用不必要的子查询，尤其是在 WHERE 子句中。

📘 参考资料