SQL 实战：联合查询与子查询 – 数据比对与关联查询优化

在复杂的数据分析和开发场景中，我们经常需要对多张表的数据进行比对和关联查询，以满足复杂业务需求。SQL 提供了丰富的查询方式，包括 JOIN、UNION 和子查询，这些技术在处理多表关联、交叉比对以及过滤特定数据时非常高效。

本文将详细介绍 JOIN、UNION 和子查询 的核心用法，并通过实际案例展示如何利用这些方法进行数据比对与查询优化。

一、核心概念与区别

1. JOIN（连接查询）

JOIN 主要用于多表之间的行级数据关联，可以根据相关字段将多张表的数据合并在一起。

• INNER JOIN：返回两张表中符合匹配条件的记录。
• LEFT JOIN：返回左表的所有记录，如果右表没有匹配，返回 NULL。
• RIGHT JOIN：返回右表的所有记录，如果左表没有匹配，返回 NULL。
• FULL JOIN（部分数据库支持）：返回两张表中所有匹配和不匹配的记录。

示例：

SELECT *  
FROM orders o  
INNER JOIN customers c  
ON o.customer_id = c.customer_id;

2. UNION（合并查询）

UNION 将多次 SELECT 查询的结果合并到一起，返回去重后的记录。

• UNION ALL：合并结果但不去重，性能更高。

示例：

SELECT name FROM employees  
UNION  
SELECT name FROM managers;

3. 子查询（Subquery）

子查询是一种嵌套查询，可以在 SELECT、FROM 和 WHERE 等语句中使用，通常用于复杂的条件筛选或分层查询。

• EXISTS / NOT EXISTS：判断子查询结果是否存在，适合在关联过滤中使用。
• IN / NOT IN：用于判断某字段是否在子查询结果集范围内。

示例：

SELECT name FROM customers  
WHERE customer_id IN (SELECT customer_id FROM orders WHERE amount > 1000);

二、实战案例：复杂数据比对与关联查询

案例 1：查询购买过商品 A 但未购买商品 B 的用户

场景：
电商系统中，用户可以购买不同的商品。我们希望找出购买了商品 A 但从未购买商品 B 的用户，以便进行针对性的营销活动。

表结构

• users：用户表
  | user_id | name |
  |---------|-------|
  | 1 | 张三 |
  | 2 | 李四 |
  | 3 | 王五 |

• orders：订单表
  | order_id | user_id | product_name |
  |----------|---------|--------------|
  | 101 | 1 | 商品A |
  | 102 | 2 | 商品B |
  | 103 | 1 | 商品C |
  | 104 | 3 | 商品A |
  | 105 | 3 | 商品B |

方法 1：使用 LEFT JOIN + NULL 判断

SELECT u.user_id, u.name  
FROM users u  
LEFT JOIN orders a ON u.user_id = a.user_id AND a.product_name = '商品A'  
LEFT JOIN orders b ON u.user_id = b.user_id AND b.product_name = '商品B'  
WHERE a.order_id IS NOT NULL  
  AND b.order_id IS NULL;

结果：

解释：

• 第一个 LEFT JOIN 过滤出购买过商品 A 的用户。
• 第二个 LEFT JOIN 尝试关联商品 B，如果为空表示未购买。
• 使用 WHERE b.order_id IS NULL 过滤出未购买商品 B 的用户。

方法 2：使用 NOT EXISTS 子查询

SELECT u.user_id, u.name  
FROM users u  
WHERE EXISTS (
    SELECT 1 FROM orders o  
    WHERE o.user_id = u.user_id AND o.product_name = '商品A'  
)  
AND NOT EXISTS (
    SELECT 1 FROM orders o  
    WHERE o.user_id = u.user_id AND o.product_name = '商品B'  
);

结果：

解释：

• EXISTS 子查询 确认用户购买过商品 A。
• NOT EXISTS 子查询 过滤掉购买过商品 B 的用户。

方法 3：使用 NOT IN 子查询

SELECT u.user_id, u.name  
FROM users u  
WHERE u.user_id IN (
    SELECT user_id FROM orders WHERE product_name = '商品A'  
)  
AND u.user_id NOT IN (
    SELECT user_id FROM orders WHERE product_name = '商品B'  
);

结果：

解释：

• 使用 IN 和 NOT IN 简单直接，适合小数据量场景。
• 适用于数据量不大时，IN 查询的执行速度较快。

三、复杂多表关联与优化技巧

案例 2：查询每个用户的购买总额和订单数量

需求：统计每个用户的总购买金额和订单数量，列出用户的订单汇总信息。

SQL 实现

SELECT u.user_id, u.name,  
       COUNT(o.order_id) AS total_orders,  
       SUM(o.amount) AS total_amount  
FROM users u  
LEFT JOIN orders o ON u.user_id = o.user_id  
GROUP BY u.user_id, u.name  
ORDER BY total_amount DESC;

解释：

• 使用 LEFT JOIN 确保即使用户没有订单记录，也能在统计中体现。
• GROUP BY 进行分组统计，每个用户作为一组计算订单数量和总金额。

四、关联查询优化技巧

1. 避免嵌套子查询：

• 将嵌套子查询改写为 JOIN，减少重复扫描表的次数。
• 在大数据量场景中，JOIN 通常比子查询性能更优。

优化示例：

-- 子查询方式（低效）
SELECT name FROM users  
WHERE user_id IN (SELECT user_id FROM orders WHERE amount > 500);  

-- JOIN 优化方式
SELECT DISTINCT u.name  
FROM users u  
JOIN orders o ON u.user_id = o.user_id  
WHERE o.amount > 500;

2. 使用 EXISTS 替代 IN：
   EXISTS 比 IN 更适合大数据量查询，因为 EXISTS 一旦匹配到结果就返回，不需要扫描完整表。

SELECT name FROM users u  
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.user_id AND o.amount > 500
);

3. 索引优化：

• 在关联字段和过滤字段上建立索引，提升 JOIN 和子查询的执行速度。

CREATE INDEX idx_orders_user_id ON orders(user_id);

五、总结

• JOIN 适用于多表关联，子查询适用于复杂的条件筛选。
• EXISTS 和 NOT EXISTS 在处理数据比对时非常高效，适合大数据量关联过滤。
• 使用 LEFT JOIN + NULL 判断 是解决差集问题的常用方式。
• 在实际项目中，合理选择 JOIN、UNION 和子查询可以显著提升查询效率。