Subquery子查询能嵌套吗

可以的，SQL 中的子查询（Subquery）是可以嵌套的，也就是“子查询里再写子查询”。

一、什么是嵌套子查询

嵌套子查询指的是：一个子查询的结果，作为另一个子查询的输入或条件，可以多层嵌套。

SELECT *
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM departments
    WHERE location_id = (
        SELECT location_id
        FROM locations
        WHERE city = 'Beijing'
    )
);

上面例子中：

• 最内层子查询找 city = 'Beijing' 的 location_id
• 中间子查询用这个 location_id 找 department_id
• 最外层查询用 department_id 查员工

二、常见嵌套形式

1. WHERE 中嵌套

SELECT *
FROM t1
WHERE col1 = (SELECT col2 FROM t2 WHERE col3 = (SELECT col4 FROM t3));

2. SELECT 中嵌套

SELECT 
    id,
    (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

3. FROM 中嵌套（派生表）

SELECT *
FROM (
    SELECT *
    FROM (
        SELECT * FROM sales
    ) t1
) t2;

三、注意事项

• 可读性：嵌套层级过多会降低 SQL 可读性和维护性
• 性能：深层嵌套可能导致执行计划复杂，性能下降
• 可替代方案：
  • JOIN
  • WITH（CTE，公共表表达式）

WITH loc AS (
    SELECT location_id FROM locations WHERE city = 'Beijing'
),
dept AS (
    SELECT department_id FROM departments WHERE location_id IN (SELECT location_id FROM loc)
)
SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM dept);

四、总结

✅ 子查询可以嵌套
⚠ 但不建议嵌套太深
✅ 复杂逻辑优先考虑 JOIN 或 CTE

如果你有具体 SQL 场景，我可以帮你改写或优化。