UNION ALL vs UNION，别再无脑用UNION了

-- ❌ 不需要去重的场景用了UNION，白白多一次Shuffle
SELECT user_id, event_name, dt FROM app_event WHERE dt = '2025-01-01'
UNION
SELECT user_id, event_name, dt FROM web_event WHERE dt = '2025-01-01'
 
-- ✅ 用UNION ALL，快几十倍
SELECT user_id, event_name, dt FROM app_event WHERE dt = '2025-01-01'
UNION ALL
SELECT user_id, event_name, dt FROM web_event WHERE dt = '2025-01-01'

-- 两个渠道都可能记录同一个用户的注册
SELECT user_id, register_date FROM channel_a_register
UNION
SELECT user_id, register_date FROM channel_b_register

SELECT user_id, register_date FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY register_date) AS rn
  FROM (
    SELECT user_id, register_date FROM channel_a_register
    UNION ALL
    SELECT user_id, register_date FROM channel_b_register
  ) combined
) t
WHERE rn = 1

-- 获取所有出现过的渠道名（去重）
SELECT channel FROM app_event
UNION
SELECT channel FROM web_event

SELECT name, '语文' AS subject, chinese AS score FROM score_wide
UNION ALL
SELECT name, '数学' AS subject, math AS score FROM score_wide
UNION ALL
SELECT name, '英语' AS subject, english AS score FROM score_wide

用法二：合并不同粒度的汇总

做报表经常需要在同一张表里输出明细和汇总：

-- 每个渠道的消耗 + 总计
SELECT channel, SUM(cost) AS total_cost
FROM ad_daily_cost
WHERE dt = '2025-01-01'
GROUP BY channel
 
UNION ALL
 
SELECT '总计' AS channel, SUM(cost) AS total_cost
FROM ad_daily_cost
WHERE dt = '2025-01-01'

这比用GROUPING SETS更直观。当然如果汇总维度很多，GROUPING SETS会更简洁：

SELECT
  COALESCE(channel, '总计') AS channel,
  COALESCE(region, '全部') AS region,
  SUM(cost) AS total_cost
FROM ad_daily_cost
WHERE dt = '2025-01-01'
GROUP BY GROUPING SETS (
  (channel, region),
  (channel),
  ()
)

用法三：分段处理不同逻辑

当不同条件需要完全不同的计算逻辑时，拆成多个查询再UNION ALL是最清晰的写法：

-- 活跃用户：最近7天有登录
SELECT user_id, '活跃' AS status, MAX(login_date) AS last_date
FROM user_login
WHERE login_date >= DATE_SUB(current_date(), 7)
GROUP BY user_id
 
UNION ALL
 
-- 沉默用户：7-30天没登录
SELECT user_id, '沉默' AS status, MAX(login_date) AS last_date
FROM user_login
GROUP BY user_id
HAVING MAX(login_date) BETWEEN DATE_SUB(current_date(), 30) AND DATE_SUB(current_date(), 8)
 
UNION ALL
 
-- 流失用户：超过30天没登录
SELECT user_id, '流失' AS status, MAX(login_date) AS last_date
FROM user_login
GROUP BY user_id
HAVING MAX(login_date) < DATE_SUB(current_date(), 30)

用法四：INSERT多分区

Hive中一次写入多个分区，可以用UNION ALL配合动态分区：

INSERT OVERWRITE TABLE result PARTITION(dt)
SELECT user_id, metric_value, '2025-01-01' AS dt
FROM source WHERE dt = '2025-01-01'
UNION ALL
SELECT user_id, metric_value, '2025-01-02' AS dt
FROM source WHERE dt = '2025-01-02'

用法五：补全缺失数据

DAY 7讲同比环比时提到的日历表补0，用的就是UNION ALL的思路：

-- 真实数据
SELECT dt, channel, cost FROM ad_daily_cost
 
UNION ALL
 
-- 补全没有数据的日期
SELECT d.dt, c.channel, 0 AS cost
FROM date_dim d
CROSS JOIN (SELECT DISTINCT channel FROM ad_daily_cost) c
WHERE NOT EXISTS (
  SELECT 1 FROM ad_daily_cost a
  WHERE a.dt = d.dt AND a.channel = c.channel
)

面试注意事项

1. UNION ALL的列数和类型必须一致

-- ❌ 列数不同会报错
SELECT user_id, user_name FROM table_a
UNION ALL
SELECT user_id FROM table_b
 
-- ❌ 类型不兼容可能报错或隐式转换
SELECT user_id, cost FROM table_a     -- cost是DECIMAL
UNION ALL
SELECT user_id, event_name FROM table_b -- event_name是STRING

2. ORDER BY只能写在最外层

-- ❌ 每个子查询单独排序无效（会被优化器忽略）
SELECT * FROM table_a ORDER BY dt
UNION ALL
SELECT * FROM table_b ORDER BY dt
 
-- ✅ 在最外层排序
SELECT * FROM (
  SELECT * FROM table_a
  UNION ALL
  SELECT * FROM table_b
) t
ORDER BY dt

3. UNION ALL可以超过两个

没有限制，可以UNION ALL十几个查询，但要注意可读性。如果超过5个，建议用CTE（WITH语句）拆分。

总结

对比	UNION ALL	UNION
------	----------	-------
是否去重	否	是
性能	快（无额外操作）	慢（需要Shuffle去重）
使用场景	数据源不重叠	明确需要去重
使用频率	90%	10%

一句话原则：默认用UNION ALL，除非你能说清楚为什么需要去重。

明天 DAY 17 聊SQL执行计划怎么看，EXPLAIN的输出很多人觉得"看不懂"，但其实只要抓住几个关键信息，就能快速定位性能瓶颈。

你在工作中有没有因为用了UNION而导致任务变慢的经历？评论区聊聊～

关注「Wood聊大数据」| 9年大数据老兵，聊技术、聊职场、聊成长