1、子查詢作為過濾條件（WHERE子句中）

場景：查詢與"張三"同部門的員工（不包含張三本人）

SELECT employee_id, name, department
FROM employees
WHERE department = (
  -- 子查詢：獲取張三所在的部門
  SELECT department 
  FROM employees 
  WHERE name = '張三'
)
AND name != '張三'; -- 排除張三本人

2、子查詢與IN運算符結(jié)合

場景：查詢已下過訂單的用戶信息

SELECT user_id, username, email
FROM users
WHERE user_id IN (
  -- 子查詢：獲取所有有訂單記錄的用戶ID
  SELECT DISTINCT user_id 
  FROM orders
);

3、子查詢與EXISTS運算符結(jié)合

場景：查詢存在未付款訂單的用戶（EXISTS更高效，找到匹配即停止）

SELECT user_id, username
FROM users u
WHERE EXISTS (
  -- 關(guān)聯(lián)子查詢：檢查該用戶是否有未付款訂單
  SELECT 1 
  FROM orders o 
  WHERE o.user_id = u.user_id 
    AND o.status = 'unpaid'
);

4、子查詢作為計算字段（SELECT列表中）

場景：查詢每個產(chǎn)品及其所屬類別的平均價格

SELECT 
  product_id,
  product_name,
  price,
  -- 子查詢：計算當前產(chǎn)品所屬類別的平均價格
  (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、關(guān)聯(lián)子查詢（引用外部表字段）

場景：查詢每個用戶的首單訂單信息

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 -- 關(guān)聯(lián)外部表的user_id
);

7、子查詢與比較運算符結(jié)合

場景：查詢價格高于所有產(chǎn)品平均價格的商品

SELECT product_id, product_name, price
FROM products
WHERE price > (
  -- 子查詢：計算所有產(chǎn)品的平均價格
  SELECT AVG(price) 
  FROM products
);

8、多層嵌套子查詢

場景：查詢購買過"電子產(chǎn)品"類商品的用戶（三層嵌套）

SELECT user_id, username
FROM users
WHERE user_id IN (
  -- 第二層：獲取購買過特定產(chǎn)品ID的用戶
  SELECT DISTINCT user_id 
  FROM orders 
  WHERE product_id IN (
    -- 第一層：獲取"電子產(chǎn)品"類的所有產(chǎn)品ID
    SELECT product_id 
    FROM products 
    WHERE category = '電子產(chǎn)品'
  )
);

9、子查詢與ANY運算符結(jié)合

場景：查詢薪資高于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運算符結(jié)合

場景：查詢價格高于所有書籍類商品的電子產(chǎn)品

SELECT product_id, product_name, price
FROM products
WHERE category = '電子產(chǎn)品'
  AND price > ALL (  -- ALL：價格需高于書籍類所有商品
    SELECT price
    FROM products
    WHERE category = '書籍'
);

11、子查詢在HAVING子句應(yīng)用

場景：查詢訂單總量超過該用戶平均訂單金額的用戶

SELECT user_id, SUM(amount) AS total_orders
FROM orders
GROUP BY user_id
HAVING SUM(amount) > (  -- HAVING子句過濾分組結(jié)果
    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 及以下）中可能因 "非聚合列引用" 產(chǎn)生警告，但不屬于語法錯誤，且在現(xiàn)代數(shù)據(jù)庫（如：MySQL 8.0、PostgreSQL）中完全支持。

12、相關(guān)子查詢更新數(shù)據(jù)

場景：將庫存量低于該類商品平均庫存的商品標記為緊缺

UPDATE products
SET status = '緊缺'
WHERE stock < (  -- 更新條件：當前商品庫存 < 同類平均
    SELECT AVG(stock)
    FROM products p2
    WHERE p2.category = products.category  -- 關(guān)聯(lián)當前商品類別
);

13、子查詢實現(xiàn)分頁優(yōu)化

場景：查詢第11-20位薪資最高的員工

SELECT employee_id, name, salary
FROM employees
WHERE employee_id IN (
    SELECT employee_id
    FROM (
        -- 通過行號實現(xiàn)高效分頁
        SELECT employee_id, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
        FROM employees
    ) AS ranked
    WHERE rn BETWEEN 11 AND 20  -- 精準定位頁碼區(qū)間
);

14、子查詢插入關(guān)聯(lián)數(shù)據(jù)

場景：為未注冊會員的訂單用戶自動創(chuàng)建會員賬號

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、子查詢實現(xiàn)遞歸邏輯

場景：查詢所有間接下屬（無限層級組織架構(gòu)）

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;  -- 最終返回所有層級結(jié)果

? 子查詢使用注意事項

1、括號強制：子查詢必須用()包裹

2、別名要求：FROM子句中的子查詢必須指定別名（如AS temp）

3、返回結(jié)果匹配：

• 單行子查詢（單值）可用 =, >, <
• 多行子查詢必須用 IN, ANY, ALL, EXISTS

4、關(guān)聯(lián)機制：內(nèi)部查詢可訪問外部查詢字段（例：WHERE cat_id = outer.cat_id）

5、性能陷阱：

• 避免超過3層嵌套（改用CTE或臨時表優(yōu)化）
• 關(guān)聯(lián)子查詢需確保連接字段有索引