如何改写子查询为连接

把子查询（Subquery）改写为连接（JOIN）是 SQL 优化中非常常见的做法，通常可以提升可读性和执行效率。下面按常见场景给你讲怎么改，并附例子。

一、IN 子查询 → JOIN

原写法（子查询）

SELECT *
FROM orders
WHERE customer_id IN (
    SELECT id
    FROM customers
    WHERE country = 'China'
);

改写为 JOIN

SELECT o.*
FROM orders o
JOIN customers c
  ON o.customer_id = c.id
WHERE c.country = 'China';

✅ 适合：

• IN (SELECT ...)
• 子查询返回单列主键/外键

二、EXISTS 子查询 → JOIN（或 SEMI JOIN）

原写法

SELECT *
FROM orders o
WHERE EXISTS (
    SELECT 1
    FROM customers c
    WHERE c.id = o.customer_id
      AND c.country = 'China'
);

改写为 JOIN

SELECT o.*
FROM orders o
JOIN customers c
  ON o.customer_id = c.id
WHERE c.country = 'China';

⚠️ 注意：

• 如果 一对多，JOIN 可能“放大行数”
• 此时可加 DISTINCT 或用 EXISTS 更安全

SELECT DISTINCT o.*
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'China';

三、子查询在 SELECT 中（标量子查询）→ LEFT JOIN

原写法

SELECT 
    o.id,
    (SELECT MAX(amount)
     FROM payments p
     WHERE p.order_id = o.id) AS max_pay
FROM orders o;

改写为 JOIN

SELECT 
    o.id,
    MAX(p.amount) AS max_pay
FROM orders o
LEFT JOIN payments p
  ON p.order_id = o.id
GROUP BY o.id;

✅ 适合：

• 聚合类子查询（MAX / MIN / COUNT / SUM）

四、FROM 中的子查询 → 直接 JOIN

原写法

SELECT *
FROM orders o
JOIN (
    SELECT customer_id, COUNT(*) cnt
    FROM orders
    GROUP BY customer_id
) t
ON o.customer_id = t.customer_id;

改写为 CTE / 直接 JOIN（视数据库）

WITH t AS (
    SELECT customer_id, COUNT(*) cnt
    FROM orders
    GROUP BY customer_id
)
SELECT *
FROM orders o
JOIN t ON o.customer_id = t.customer_id;

五、NOT IN / NOT EXISTS → LEFT JOIN + IS NULL

原写法

SELECT *
FROM customers c
WHERE c.id NOT IN (
    SELECT customer_id
    FROM orders
);

改写为 JOIN

SELECT c.*
FROM customers c
LEFT JOIN orders o
  ON c.id = o.customer_id
WHERE o.customer_id IS NULL;

⚠️ 注意：

• NOT IN 遇到 NULL 会出错
• LEFT JOIN ... IS NULL 更安全

六、改写原则总结

✅ 优先用 JOIN 当：

• 子查询用于过滤
• 子查询用于关联维度表
• 子查询用于聚合

⚠️ 保留子查询当：

• 相关子查询逻辑复杂
• 一对多会导致结果膨胀
• 数据库优化器对 EXISTS 更友好

如果你有具体的 SQL 语句，可以直接发出来，我可以帮你逐行改写成 JOIN 并说明是否更优。