如何写嵌套SQL查询

嵌套 SQL 查询（Nested Query / Subquery）是指在一个 SQL 语句中嵌套另一个 SQL 查询，通常外层查询依赖内层查询的结果。下面从概念 → 分类 → 写法示例 → 注意事项系统讲一下。

一、什么是嵌套 SQL 查询

**子查询（Subquery）**是写在 SELECT / FROM / WHERE / HAVING 中的查询语句，用括号 () 包裹。

SELECT *
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location = 'Beijing'
);

二、按“位置”分类的嵌套查询

1️⃣ WHERE 中的子查询（最常见）

用于过滤数据

✅ 标量子查询（返回 1 个值）

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

✅ IN 子查询（返回多行）

SELECT name
FROM employees
WHERE department_id IN (
    SELECT id
    FROM departments
    WHERE status = 'active'
);

✅ EXISTS 子查询（判断是否存在）

SELECT d.name
FROM departments d
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.id
);

2️⃣ FROM 中的子查询（派生表）

子查询当作一张临时表

SELECT dept_id, avg_salary
FROM (
    SELECT department_id AS dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) t
WHERE avg_salary > 10000;

⚠️ 子查询必须有别名（如 t）

3️⃣ SELECT 中的子查询

用于计算字段

SELECT name,
       (SELECT COUNT(*)
        FROM orders o
        WHERE o.customer_id = c.id) AS order_count
FROM customers c;

4️⃣ HAVING 中的子查询

SELECT department_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (
    SELECT AVG(salary)
    FROM employees
);

三、按“相关性”分类

✅ 非相关子查询

子查询不依赖外层

SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

✅ 相关子查询

子查询引用外层字段

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

四、多层嵌套示例

SELECT name
FROM employees
WHERE department_id IN (
    SELECT id
    FROM departments
    WHERE location IN (
        SELECT city
        FROM regions
        WHERE country = 'China'
    )
);

五、常见错误 & 注意点

❌ 子查询返回多行却用 =
✅ 用 IN 或 EXISTS

❌ FROM 子查询没别名
✅ 一定要写 AS t

❌ 相关子查询性能差
✅ 可用 JOIN 优化

六、子查询 vs JOIN（经验建议）

• 逻辑清晰 → 子查询
• 性能优先 → JOIN
• 多层嵌套 → 考虑 CTE（WITH）

WITH avg_sal AS (
    SELECT department_id, AVG(salary) s
    FROM employees
    GROUP BY department_id
)
SELECT e.name
FROM employees e
JOIN avg_sal a ON e.department_id = a.department_id
WHERE e.salary > a.s;

如果你有具体业务场景 / 数据库（MySQL、PostgreSQL、Oracle），我可以帮你写可直接用的嵌套 SQL。