SQL 子查询详解及示例

什么是 SQL 子查询？

子查询，也称为内部查询或嵌套查询，是指嵌入在另一个 SQL 查询中的查询。它用括号括起来，先于外部查询执行，并为外部查询提供结果用于进一步处理。

子查询类型

1. 单行子查询: 返回单行结果，通常与 =、 等比较运算符结合使用。

示例: 查找薪资最高的员工姓名：

SELECT name
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

2. 多行子查询: 返回多行结果，通常与 IN、ANY、ALL 等运算符结合使用。

示例: 查找在员工数超过 5 人的部门工作的员工：

SELECT name
FROM employees
WHERE departmentid IN (SELECT departmentid FROM departments WHERE members > 5);

3. 相关子查询: 引用外部查询中的列，针对外部查询的每一行重复执行。

示例: 查找薪资高于所在部门平均薪资的员工：

SELECT name, salary
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE departmentid = e.departmentid);

4. 嵌套子查询: 包含另一个子查询的子查询。

示例: 查找平均薪资超过 50,000 美元的部门：

SELECT departmentname
FROM departments
WHERE departmentid IN (
    SELECT departmentid
    FROM employees
    WHERE salary > (SELECT AVG(salary) FROM employees)
);

子查询应用场景

1. 数据过滤: 根据特定条件过滤数据行。

SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);

2. 数据比较: 比较不同表中的数据。

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

3. 数据聚合: 执行高级聚合操作。

SELECT departmentid, (SELECT COUNT(*) FROM employees WHERE departmentid = d.id) AS employeecount
FROM departments d;

4. 动态更新: 基于子查询动态更新或删除数据行。

UPDATE Employees
SET Bonus = 500
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

子查询优势

1. 模块化: 将复杂查询分解成更小、更易管理的单元。
2. 可读性: 提升查询逻辑的清晰度。
3. 灵活性: 可用于 SELECT、WHERE、FROM 和 HAVING 子句。

子查询局限性

1. 性能: 子查询，尤其是相关子查询，多次执行可能导致性能下降。
2. 复杂性: 深度嵌套的子查询可能难以阅读和调试。
3. 优化挑战: 有时，使用连接可以获得更高的效率。

总结

子查询是 SQL 中强大的工具，用于将复杂逻辑分解成可管理的单元，实现动态数据分析和数据操作。然而，需要权衡子查询与连接等替代方案，以获得最佳查询性能。