FULL OUTER JOIN 和 LEFT JOIN 的区别 | 使用 UNION ALL 替代 FULL OUTER JOIN 有什么好处

原创已于 2025-11-30 23:01:04 修改 · 251 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #sql #join

于 2025-10-30 08:57:50 首次发布

数据库专栏收录该内容

35 篇文章

订阅专栏

文章目录

JOIN 类型对比

INNER JOIN（内连接）

SELECT * FROM table_a a INNER JOIN table_b b ON a.id = b.id

结果：只返回两个表中都存在的记录 交集部分

LEFT JOIN（左外连接）

SELECT * FROM table_a a LEFT JOIN table_b b ON a.id = b.id

结果：返回左表的所有记录，右表匹配则显示，不匹配则右表字段为NULL。 左表全部 + 右表匹配的部分

RIGHT JOIN（右外连接）

SELECT * FROM table_a a RIGHT JOIN table_b b ON a.id = b.id

结果：返回右表的所有记录，左表匹配则显示，不匹配则左表字段为NULL。右表全部 + 左表匹配的部分

FULL OUTER JOIN（全外连接）

SELECT * FROM table_a a FULL OUTER JOIN table_b b ON a.id = b.id

结果：返回两个表的所有记录，无论是否匹配。左表全部 + 右表全部

实际例子演示

假设有两个表：
table_a（手动编码数据）

time_point | manual_count
-----------|-------------
2025-10-21 |     100
2025-10-23 |     200

table_b（AI生成数据）

time_point | ai_count
-----------|----------
2025-10-22 |   1000
2025-10-23 |   2000
2025-10-24 |   3000

使用 LEFT JOIN：

SELECT 
    a.time_point,
    a.manual_count,
    b.ai_count
FROM table_a a
LEFT JOIN table_b b ON a.time_point = b.time_point

结果：

time_point | manual_count | ai_count
-----------|--------------|----------
2025-10-21 |     100      |   NULL    ← 手动编码的日期，无AI数据
2025-10-23 |     200      |   2000    ← 两者都有的日期

使用 FULL OUTER JOIN：

SELECT 
    COALESCE(a.time_point, b.time_point) as time_point,
    a.manual_count,
    b.ai_count
FROM table_a a
FULL OUTER JOIN table_b b ON a.time_point = b.time_point

结果：

time_point | manual_count | ai_count
-----------|--------------|----------
2025-10-21 |     100      |   NULL    ← 只有手动编码
2025-10-22 |     NULL     |   1000    ← 只有AI生成  
2025-10-23 |     200      |   2000    ← 都有
2025-10-24 |     NULL     |   3000    ← 只有AI生成

案例中FULL OUTER JOIN 中使用 COALESCE(a.time_point, b.time_point) 问题点

问题描述：
如果某一行中 a.time_point 和 b.time_point 都为 NULL，那么 COALESCE(a.time_point, b.time_point) 结果也会是 NULL。这在语义上意味着这一行的时间信息完全缺失。

解决思路：
使用「日期维度表」+ LEFT JOIN 确保每一天都有数据点

clickhouse sql demo：
日期维度的基础 CTE 来完成 “LEFT JOIN” 补全时间的目的

WITH 
startDate := '2025-10-20',
endDate := '2025-10-25',

date_range AS (
    SELECT 
        addDays(toDate(startDate), number) AS time_point
    FROM numbers(dateDiff('day', toDate(startDate), toDate(endDate)) + 1)
)

SELECT 
    d.time_point,
    a.manual_count,
    b.ai_count
FROM date_range d
LEFT JOIN table_a a ON d.time_point = a.time_point
LEFT JOIN table_b b ON d.time_point = b.time_point
ORDER BY d.time_point;

使用 UNION ALL 替代 FULL OUTER JOIN 有什么好处

UNION ALL 会将两张表的所有记录合并在一起，包括重复的记录。它不会去除重复项，也不会进行任何匹配。具体来说：

表 A 的所有记录会被列出。
表 B 的所有记录会被列出。
如果表 A 和表 B 中有相同的记录，它们会在结果中重复出现。

UNION ALL 允许分别查询两个表，然后将结果集合并，而无需复杂的合并逻辑，并且在结果集较大时可以显著减少资源消耗。

FULL OUTER JOIN 在 ClickHouse 中的支持相对较弱，某些版本甚至不支持。

性能优势

UNION ALL：
* 不需要进行连接操作，各个子查询可以并行执行（每个 UNION 分支可以独立执行，减少锁等待时间）
* 没有 JOIN 的匹配开销，直接合并结果集
* 对于大数据量，性能通常更好
FULL OUTER JOIN：
* 需要在两个表之间进行匹配操作
* 可能产生笛卡尔积的风险
* 需要额外的内存来维护 JOIN 的哈希表或排序

更好的分布式性能

-- 在分布式表上，UNION ALL 避免了全局 JOIN 的网络开销
-- 每个分支可以在本地节点执行，减少数据传输

SELECT * FROM dist_table_a WHERE condition_a
UNION ALL  
SELECT * FROM dist_table_b WHERE condition_b

可预测的结果：
UNION ALL 操作简单地将两个结果集垂直堆叠起来，生成的结果集更易于理解，而 FULL OUTER JOIN 的行为可能更复杂，并且难以预测。
灵活性：
UNION ALL 允许用户对每个表的查询进行优化，例如对每个表应用不同的筛选条件，然后再将结果合并。
使用 UNION ALL ，添加新的维度非常简单：

manual_row AS (...),
ai_rows AS (...),
other_rows AS (...)  -- 新增维度

SELECT * FROM manual_row
UNION ALL
SELECT * FROM ai_rows
UNION ALL
SELECT * FROM other_rows  -- 轻松扩展

选择 UNION ALL 的信号

选择 UNION ALL 的信号:

逻辑互斥：数据来自不同的业务分类
例如：手动 vs AI、付费 vs 免费、线上 vs 线下
结构相同：子查询的列数、列类型完全一致
必须保证 SELECT 的字段顺序和类型匹配
无需匹配：不需要根据某个字段关联两个数据集
只是简单地"堆叠"数据
性能敏感：大数据量场景
UNION ALL 不去重，比 UNION 快
避免 JOIN 的哈希表开销

不要用 UNION ALL 的信号:

需要匹配：数据需要根据某个键关联
需要去重：合并后的数据可能有重复
列结构不匹配：子查询的列数或类型不一致

总结
UNION ALL 的核心使用场景：
✅ 异构数据合并：不同来源/分类的数据（例如：同一个表不同的事件类型，数据通过 WHERE 条件分成两类，彼此不重叠。关键点：同一行数据不会同时满足这两个条件）
✅ 时间分区合并：跨时间段查询
✅ 汇总+明细：既要分组汇总，又要展示总计
✅ 多表结构相似：不同业务实体的统一视图
✅ A/B测试：实验分组数据对比
关键判断标准：
数据是否逻辑互斥、不需要匹配？→ UNION ALL
数据是否需要根据某字段关联？→ JOIN

既然都在同一个表,为什么不直接用一个查询 + CASE WHEN？
一般是因为下面的原因：