1、子查询作为过滤条件（WHERE子句中）

场景：查询与"张三"同部门的员工（不包含张三本人）

SELECT employee_id, name, department
FROM employees
WHERE department = (
  -- 子查询：获取张三所在的部门
  SELECT department 
  FROM employees 
  WHERE name = '张三'
)
AND name != '张三'; -- 排除张三本人

2、子查询与IN运算符结合

场景：查询已下过订单的用户信息

SELECT user_id, username, email
FROM users
WHERE user_id IN (
  -- 子查询：获取所有有订单记录的用户ID
  SELECT DISTINCT user_id 
  FROM orders
);

3、子查询与EXISTS运算符结合

场景：查询存在未付款订单的用户（EXISTS更高效，找到匹配即停止）

SELECT user_id, username
FROM users u
WHERE EXISTS (
  -- 关联子查询：检查该用户是否有未付款订单
  SELECT 1 
  FROM orders o 
  WHERE o.user_id = u.user_id 
    AND o.status = 'unpaid'
);

4、子查询作为计算字段（SELECT列表中）

场景：查询每个产品及其所属类别的平均价格

SELECT 
  product_id,
  product_name,
  price,
  -- 子查询：计算当前产品所属类别的平均价格
  (SELECT AVG(price) 
   FROM products p2 
   WHERE p2.category = p1.category) AS category_avg_price
FROM products p1;

5、子查询作为表（FROM子句中，派生表）

场景：查询每个部门的平均工资及高于该部门平均工资的员工

SELECT e.employee_id, e.name, e.salary, dept_avg.avg_salary
FROM employees e
JOIN (
  -- 子查询：计算各部门平均工资
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) AS dept_avg ON e.department = dept_avg.department
WHERE e.salary > dept_avg.avg_salary; -- 筛选高于部门平均工资的员工

6、关联子查询（引用外部表字段）

场景：查询每个用户的首单订单信息

SELECT o.order_id, o.user_id, o.order_time
FROM orders o
WHERE o.order_time = (
  -- 子查询：获取当前用户的最早订单时间
  SELECT MIN(order_time) 
  FROM orders 
  WHERE user_id = o.user_id -- 关联外部表的user_id
);

7、子查询与比较运算符结合

场景：查询价格高于所有产品平均价格的商品

SELECT product_id, product_name, price
FROM products
WHERE price > (
  -- 子查询：计算所有产品的平均价格
  SELECT AVG(price) 
  FROM products
);

8、多层嵌套子查询

场景：查询购买过"电子产品"类商品的用户（三层嵌套）

SELECT user_id, username
FROM users
WHERE user_id IN (
  -- 第二层：获取购买过特定产品ID的用户
  SELECT DISTINCT user_id 
  FROM orders 
  WHERE product_id IN (
    -- 第一层：获取"电子产品"类的所有产品ID
    SELECT product_id 
    FROM products 
    WHERE category = '电子产品'
  )
);

9、子查询与ANY运算符结合

场景：查询薪资高于IT部门任意员工的销售部门员工

SELECT employee_id, name, salary
FROM employees
WHERE department = '销售部'
  AND salary > ANY (  -- ANY：当前薪资需大于IT部门任意员工薪资
    SELECT salary
    FROM employees
    WHERE department = 'IT部' -- 获取所有IT员工的薪资集合
);

10、子查询与ALL运算符结合

场景：查询价格高于所有书籍类商品的电子产品

SELECT product_id, product_name, price
FROM products
WHERE category = '电子产品'
  AND price > ALL (  -- ALL：价格需高于书籍类所有商品
    SELECT price
    FROM products
    WHERE category = '书籍'
);

11、子查询在HAVING子句应用

场景：查询订单总量超过该用户平均订单金额的用户

SELECT user_id, SUM(amount) AS total_orders
FROM orders
GROUP BY user_id
HAVING SUM(amount) > (  -- HAVING子句过滤分组结果
    SELECT AVG(amount)  -- 计算当前用户的平均订单金额
    FROM orders o2
    WHERE o2.user_id = orders.user_id
);

说明：子查询SELECT AVG(amount) FROM orders o2 WHERE o2.user_id = orders.user_id依赖外部分组的user_id，在部分SQL方言（如：MySQL 5.7 及以下）中可能因 "非聚合列引用" 产生警告，但不属于语法错误，且在现代数据库（如：MySQL 8.0、PostgreSQL）中完全支持。

12、相关子查询更新数据

场景：将库存量低于该类商品平均库存的商品标记为紧缺

UPDATE products
SET status = '紧缺'
WHERE stock < (  -- 更新条件：当前商品库存 < 同类平均
    SELECT AVG(stock)
    FROM products p2
    WHERE p2.category = products.category  -- 关联当前商品类别
);

13、子查询实现分页优化

场景：查询第11-20位薪资最高的员工

SELECT employee_id, name, salary
FROM employees
WHERE employee_id IN (
    SELECT employee_id
    FROM (
        -- 通过行号实现高效分页
        SELECT employee_id, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
        FROM employees
    ) AS ranked
    WHERE rn BETWEEN 11 AND 20  -- 精准定位页码区间
);

14、子查询插入关联数据

场景：为未注册会员的订单用户自动创建会员账号

INSERT INTO members(user_id, reg_date)
SELECT DISTINCT user_id, NOW() 
FROM orders o
WHERE NOT EXISTS (  -- 仅插入不存在的用户
    SELECT 1 
    FROM members m
    WHERE m.user_id = o.user_id
);

15、子查询实现递归逻辑

场景：查询所有间接下属（无限层级组织架构）

WITH RECURSIVE subordinates AS (
    -- 初始查询：直接下属
    SELECT employee_id, name 
    FROM employees
    WHERE manager_id = 1001
    
    UNION ALL
    
    -- 递归查询：逐层获取间接下属
    SELECT e.employee_id, e.name
    FROM employees e
    INNER JOIN subordinates s 
        ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates;  -- 最终返回所有层级结果

⚠ 子查询使用注意事项

1、括号强制：子查询必须用()包裹

2、别名要求：FROM子句中的子查询必须指定别名（如AS temp）

3、返回结果匹配：

• 单行子查询（单值）可用 =, >, <
• 多行子查询必须用 IN, ANY, ALL, EXISTS

4、关联机制：内部查询可访问外部查询字段（例：WHERE cat_id = outer.cat_id）

5、性能陷阱：

• 避免超过3层嵌套（改用CTE或临时表优化）
• 关联子查询需确保连接字段有索引