BigQuery中带WHERE条件的LEFT JOIN与INNER JOIN结果行数差异原因咨询

嘿，这个问题戳中了SQL连接逻辑里很容易踩坑的点，我给你掰碎了说：

先搞懂基础逻辑差异 #

首先得明确两个连接的本质：

• INNER JOIN（也就是你说的JOIN）：只认“双向匹配”——只有当左右表同时满足连接条件的行，才会被留在结果里，相当于取两张表的交集。
• LEFT JOIN：是“左表优先”——先把左表的所有行都拿出来，然后去右表里找匹配连接条件的行；找不到匹配的，右表对应的字段就填NULL，但左表的行依然保留。

关键：WHERE条件对两者的影响不一样 #

问题的核心在于WHERE子句是在连接完成后执行的过滤，这会直接改变最终的结果集，分几种常见情况来看：

情况1：WHERE条件只过滤左表字段 #

比如你写了这样的BigQuery查询：

-- LEFT JOIN版本
SELECT *
FROM left_table l
LEFT JOIN right_table r ON l.id = r.id
WHERE l.country = 'US';

-- INNER JOIN版本
SELECT *
FROM left_table l
JOIN right_table r ON l.id = r.id
WHERE l.country = 'US';

这时候：

• LEFT JOIN会返回所有美国地区的左表行——哪怕这些行在右表里没有匹配（对应的右表字段是NULL）。
• INNER JOIN只会返回美国地区且在右表里有匹配的左表行。
  显然，LEFT JOIN的行数会大于等于INNER JOIN的行数，差异就是那些“美国地区但无右表匹配”的左表行。

情况2：WHERE条件过滤右表的非NULL字段 #

比如你加了WHERE r.status = 'active'：

-- LEFT JOIN版本
SELECT *
FROM left_table l
LEFT JOIN right_table r ON l.id = r.id
WHERE r.status = 'active';

-- INNER JOIN版本
SELECT *
FROM left_table l
JOIN right_table r ON l.id = r.id
WHERE r.status = 'active';

这时候LEFT JOIN里原本左表无匹配的行（右表字段全为NULL），会因为NULL = 'active'的判断结果为UNKNOWN被WHERE过滤掉，最终结果和INNER JOIN一致。但如果你的WHERE条件是WHERE r.status IS NULL，情况就反过来了：LEFT JOIN会返回左表里没有右表匹配的行，而INNER JOIN会返回0行，行数差异极大。

情况3：把连接条件错放到WHERE里 #

很多人会踩这个坑：本该写在ON子句里的条件，写到了WHERE里。比如：

-- 错误写法（把右表过滤条件放WHERE）
SELECT *
FROM left_table l
LEFT JOIN right_table r ON l.id = r.id
WHERE r.category = 'electronics';

-- 正确写法（把右表过滤条件放ON）
SELECT *
FROM left_table l
LEFT JOIN right_table r ON l.id = r.id AND r.category = 'electronics';

第一种写法里，WHERE会过滤掉所有右表字段为NULL的行（也就是左表无匹配的行），结果和INNER JOIN一样；而第二种写法会保留左表所有行，只匹配右表里category是electronics的行，不匹配的右表字段填NULL，这时候行数就和INNER JOIN完全不同了。

总结一下 #

你看到的行数差异，本质是LEFT JOIN的“左表全保留”特性，在WHERE过滤的作用下是否被保留：

• 如果WHERE没过滤右表的NULL行，LEFT JOIN会比INNER JOIN多那些“左表有、右表无匹配”的行；
• 如果WHERE过滤了右表的NULL行，那LEFT JOIN的结果会向INNER JOIN靠拢，甚至行数相同；
• 要是WHERE专门找右表NULL的行，那LEFT JOIN会返回左表无匹配的行，INNER JOIN返回空。

内容的提问来源于stack exchange，提问作者Patryk Piątek