Laoli

The world is big, you have to go and see

Announcement

Welcome to Laoli's blog!

Learn More

1987 字

10 分钟

SQL LEFT JOIN 变 INNER JOIN：一个大数据场景下的经典踩坑

2026-03-25

Guides

SQL

/

大数据

/

Hive

SQL LEFT JOIN 变 INNER JOIN：一个大数据场景下的经典踩坑#

1. 背景#

在日常大数据开发中，我们经常需要通过多表关联来构建宽表或做数据分析。最近在做授信数据统计时，遇到了一个让人困惑的问题：

单表查询：

1
SELECT COUNT(1)
2
FROM oversea.ods_mx_aprv_approve_credit_apply_df
3
WHERE pt = '20260317'
4
  AND SUBSTR(create_time, 1, 10) = '2026-03-17'
5
  AND user_type = 1
6
  AND rule_type = 'CREDIT_APPLY'
7
  AND is_delete = 0
8
LIMIT 10;

结果：2440 条

关联查询：

1
WITH res AS (
2
    SELECT
3
        ca.id AS ca_id,
4
        ua.id AS ua_id,
5
        ca.user_type,
6
        ca.blind_lend,
7
        ca.create_time AS ca_create_time,
8
        ca.create_time AS obs_time,
9
        'CREDIT_APPLY' AS record_type,
10
        ua.asset_id
11
    FROM oversea.ods_mx_aprv_approve_credit_apply_df ca
12
        LEFT JOIN oversea.ods_mx_aprv_approve_use_credit_apply_df ua
13
            ON CAST(ca.id AS STRING) = CAST(ua.credit_apply_id AS STRING)
14
    WHERE ca.pt = '20260317'
15
        AND ua.pt = '20260317'
16
        AND SUBSTR(ca.create_time, 1, 10) = '2026-03-17'
17
)
18
SELECT COUNT(1)
19
FROM res
20
WHERE SUBSTR(ca_create_time, 1, 10) = '2026-03-17'
21
  AND record_type = 'CREDIT_APPLY'
22
  AND user_type = 1;

结果：340 条

同样的主表、同样的时间条件，为什么加了 LEFT JOIN 之后数据反而少了将近 86%？

2. 问题定位#

2.1 直觉排查#

第一反应是 LEFT JOIN 产生了数据膨胀或者去重问题，但结果是数据变少了，这就排除了膨胀的可能。

仔细审视 SQL，问题出在这一行：

1
AND ua.pt = '20260317'

这个条件写在了 WHERE 子句中，而 ua 是 LEFT JOIN 的右表。

2.2 根因分析#

核心结论：WHERE 子句中对右表字段的非空过滤，会将 LEFT JOIN 隐式转换为 INNER JOIN。

当 ca 表中某条记录在 ua 表中没有匹配时，LEFT JOIN 会将 ua 的所有字段填充为 NULL。此时 ua.pt 为 NULL，而 NULL = '20260317' 的结果是 UNKNOWN（既不是 TRUE 也不是 FALSE），WHERE 子句只保留结果为 TRUE 的行，因此这些未匹配的行被直接过滤掉了。

这意味着：只有在 ua 表中有匹配记录的 ca 行才能存活，这和 INNER JOIN 的语义完全一致。

3. 技术原理深度剖析#

3.1 SQL 的逻辑执行顺序#

要理解这个问题，必须先搞清楚 SQL 的逻辑执行顺序：

1
FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

关键点在于：ON 在 JOIN 之前执行，WHERE 在 JOIN 之后执行。

LEFT JOIN + ON 的执行流程#

1
1. FROM ca, ua          → 确定数据源
2
2. ON ca.id = ua.credit_apply_id  → 计算匹配条件
3
3. LEFT JOIN            → 执行连接，未匹配的 ca 行保留，ua 字段填 NULL
4
4. WHERE ua.pt = '...'  → 过滤！NULL 行被干掉！

LEFT JOIN + ON（正确写法）的执行流程#

1
1. FROM ca, ua          → 确定数据源
2
2. ON ca.id = ua.credit_apply_id AND ua.pt = '...'  → 匹配条件 + 分区过滤
3
3. LEFT JOIN            → 执行连接，未匹配的 ca 行保留，ua 字段填 NULL
4
4. WHERE ...            → 只过滤 ca 表的条件，NULL 行安全保留

3.2 三值逻辑（Three-Valued Logic）#

SQL 不是布尔逻辑，而是三值逻辑：TRUE、FALSE、UNKNOWN。

表达式	结果
`NULL = '20260317'`	UNKNOWN
`NULL != '20260317'`	UNKNOWN
`NULL > 0`	UNKNOWN
`NULL AND TRUE`	UNKNOWN
`NULL OR TRUE`	TRUE
`NULL OR FALSE`	UNKNOWN

WHERE 子句的规则是：只保留结果为 TRUE 的行，FALSE 和 UNKNOWN 都会被过滤。

这就是为什么 WHERE ua.pt = '20260317' 会杀死所有右表未匹配的行——因为 NULL = '20260317' 返回 UNKNOWN，直接被过滤。

3.3 Hive/Spark 的查询优化器行为#

在 Hive 和 Spark SQL 中，查询优化器会进行谓词下推（Predicate Pushdown）。当优化器检测到 WHERE 子句中存在对右表的非空过滤条件时，它会直接将 LEFT JOIN 优化为 INNER JOIN，因为语义上等价。

你可以通过 EXPLAIN 查看执行计划来验证：

1
EXPLAIN
2
SELECT *
3
FROM ca LEFT JOIN ua ON ca.id = ua.credit_apply_id
4
WHERE ua.pt = '20260317';

在执行计划中，你会看到 Join Type 已经变成了 INNER JOIN，而不是你写的 LEFT JOIN。

4. 正确写法#

4.1 将右表过滤条件移到 ON 子句#

1
WITH res AS (
2
    SELECT
3
        ca.id AS ca_id,
4
        ua.id AS ua_id,
5
        ca.user_type,
6
        ca.blind_lend,
7
        ca.create_time AS ca_create_time,
8
        ca.create_time AS obs_time,
9
        'CREDIT_APPLY' AS record_type,
10
        ua.asset_id
11
    FROM oversea.ods_mx_aprv_approve_credit_apply_df ca
12
        LEFT JOIN oversea.ods_mx_aprv_approve_use_credit_apply_df ua
13
            ON CAST(ca.id AS STRING) = CAST(ua.credit_apply_id AS STRING)
14
            AND ua.pt = '20260317'   -- ✅ 移到 ON 子句
15
    WHERE ca.pt = '20260317'
16
        AND SUBSTR(ca.create_time, 1, 10) = '2026-03-17'
17
)
18
SELECT COUNT(1)
19
FROM res
20
WHERE SUBSTR(ca_create_time, 1, 10) = '2026-03-17'
21
  AND record_type = 'CREDIT_APPLY'
22
  AND user_type = 1;

4.2 使用子查询预过滤#

1
WITH res AS (
2
    SELECT
3
        ca.id AS ca_id,
4
        ua.id AS ua_id,
5
        ca.user_type,
6
        ca.blind_lend,
7
        ca.create_time AS ca_create_time,
8
        ca.create_time AS obs_time,
9
        'CREDIT_APPLY' AS record_type,
10
        ua.asset_id
11
    FROM oversea.ods_mx_aprv_approve_credit_apply_df ca
12
        LEFT JOIN (
13
            SELECT * FROM oversea.ods_mx_aprv_approve_use_credit_apply_df
14
            WHERE pt = '20260317'   -- ✅ 在子查询中预过滤
15
        ) ua
16
            ON CAST(ca.id AS STRING) = CAST(ua.credit_apply_id AS STRING)
17
    WHERE ca.pt = '20260317'
18
        AND SUBSTR(ca.create_time, 1, 10) = '2026-03-17'
19
)
20
SELECT COUNT(1)
21
FROM res
22
WHERE SUBSTR(ca_create_time, 1, 10) = '2026-03-17'
23
  AND record_type = 'CREDIT_APPLY'
24
  AND user_type = 1;

两种写法都能保证 LEFT JOIN 的语义不被破坏，结果应为 2440 条。

5. 深度拓展#

5.1 不同 JOIN 类型下 WHERE vs ON 的行为差异#

JOIN 类型	右表条件放 ON	右表条件放 WHERE
INNER JOIN	结果相同	结果相同
LEFT JOIN	✅ 保留左表未匹配行	❌ 退化为 INNER JOIN
RIGHT JOIN	❌ 退化为 INNER JOIN	✅ 保留右表未匹配行（对左表同理）
FULL OUTER JOIN	保留两侧未匹配行	退化为对应的单侧 JOIN 或 INNER JOIN

规律：ON 条件影响的是匹配过程，WHERE 条件影响的是最终结果集。对于外连接，这两者的语义完全不同。

5.2 NULL 安全的条件写法#

如果你确实需要在 WHERE 中过滤右表字段，但又想保留未匹配行，可以加上 IS NULL 的保护：

1
WHERE (ua.pt = '20260317' OR ua.pt IS NULL)

但这种写法可读性差，且容易引入逻辑错误，不推荐。最佳实践始终是将右表条件放在 ON 子句中。

5.3 Hive 分区表的特殊陷阱#

在 Hive 中，pt 通常是分区字段。分区过滤条件 WHERE pt = '...' 是大数据查询的标配，几乎成了肌肉记忆。正是这种肌肉记忆，让我们在写 LEFT JOIN 时不假思索地把两张表的 pt 条件都丢进了 WHERE。

Hive 的分区裁剪（Partition Pruning）机制会在扫描阶段就过滤掉不需要的分区，这是一个存储层的优化。但分区裁剪和 ON/WHERE 的语义问题是两个独立的层面：

把 ua.pt = '...' 放在 ON 子句中，Hive 依然能做分区裁剪
放在 WHERE 中虽然也能裁剪分区，但会破坏 LEFT JOIN 语义

所以不用担心性能问题，放心把右表的分区条件移到 ON 里。

5.4 CAST 导致的隐式性能问题#

原始 SQL 中还有一个值得注意的点：

1
ON CAST(ca.id AS STRING) = CAST(ua.credit_apply_id AS STRING)

两边都做了 CAST，这意味着：

无法利用索引（如果有的话），因为对字段做了函数转换
增加了计算开销，每一行都需要做类型转换
可能引入精度问题，比如 BIGINT 转 STRING 后的比较

更好的做法是在建表时就统一字段类型，或者只对一侧做转换：

1
ON ca.id = CAST(ua.credit_apply_id AS BIGINT)

5.5 数据验证方法论#

当你怀疑 LEFT JOIN 被破坏时，可以用以下方法快速验证：

1
-- 方法1：检查右表字段的 NULL 占比
2
SELECT
3
    COUNT(1) AS total,
4
    COUNT(ua.id) AS matched,
5
    COUNT(1) - COUNT(ua.id) AS unmatched
6
FROM ca LEFT JOIN ua ON ...;
7

8
-- 方法2：对比 LEFT JOIN 和 INNER JOIN 的结果
9
SELECT COUNT(1) FROM ca LEFT JOIN ua ON ...;   -- 应 >= 下面的结果
10
SELECT COUNT(1) FROM ca INNER JOIN ua ON ...;  -- 如果两者相等，说明 LEFT JOIN 被破坏
11

12
-- 方法3：EXPLAIN 查看执行计划中的 Join Type
13
EXPLAIN SELECT * FROM ca LEFT JOIN ua ON ... WHERE ua.xxx = '...';

6. 总结#

要点	说明
问题本质	`WHERE` 中过滤右表字段导致 `LEFT JOIN` 退化为 `INNER JOIN`
根本原因	SQL 三值逻辑下 `NULL = value` 返回 `UNKNOWN`，被 `WHERE` 过滤
正确做法	右表过滤条件放 `ON` 子句，或使用子查询预过滤
适用范围	所有 SQL 引擎（MySQL、PostgreSQL、Hive、Spark SQL、Presto 等）

这不是 Hive 的 bug，也不是大数据特有的问题，而是 SQL 标准语义的正常行为。只是在大数据场景下，分区字段的过滤条件让这个坑更容易踩到。

记住一条铁律：外连接中，右表（或左表）的过滤条件永远放在 ON 里，不要放在 WHERE 里。