在复杂的数据分析和开发场景中，我们经常需要对多张表的数据进行比对和关联查询，以满足复杂业务需求。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;
结果：
| user_id | name |
|---|---|
| 1 | 张三 |
解释：
- 第一个 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'
);
结果：
| user_id | name |
|---|---|
| 1 | 张三 |
解释：
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'
);
结果：
| user_id | name |
|---|---|
| 1 | 张三 |
解释：
- 使用
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进行分组统计，每个用户作为一组计算订单数量和总金额。
四、关联查询优化技巧
- 避免嵌套子查询：
- 将嵌套子查询改写为
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;
- 使用 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
);
- 索引优化：
- 在关联字段和过滤字段上建立索引，提升
JOIN和子查询的执行速度。
CREATE INDEX idx_orders_user_id ON orders(user_id);
五、总结
JOIN适用于多表关联，子查询适用于复杂的条件筛选。EXISTS和NOT EXISTS在处理数据比对时非常高效，适合大数据量关联过滤。- 使用
LEFT JOIN + NULL判断 是解决差集问题的常用方式。 - 在实际项目中，合理选择
JOIN、UNION和子查询可以显著提升查询效率。
