BigQuery执行LEFT JOIN时不返回表A匹配表B为NULL的行如何解决

你写的SQL逻辑本身是正确的，是标准的左连接查找左表独有数据的写法，出现无结果返回的情况，通常是以下几个常见原因导致的，可以逐一排查：

1. 过滤条件位置错误（最高发问题） #

如果你实际执行的SQL中，对右表table_b添加了额外的过滤条件，并且把条件写在了全局WHERE子句而非JOIN的ON子句中，会直接过滤掉所有右表匹配为NULL的行，相当于左连接被生效为内连接，自然查不到结果。

错误示例：

select a.user_id
from table_a a  
left outer join table_b b on a.user_id = b.user_id 
-- 对右表的过滤条件写在WHERE中，会过滤掉b表为NULL的行
where b.dt = '2024-05-20' and b.user_id is null

正确写法：将右表的过滤条件放到ON子句中

select a.user_id
from table_a a  
left outer join table_b b on a.user_id = b.user_id and b.dt = '2024-05-20'
where b.user_id is null

2. 关联字段数据类型不匹配 #

如果两个表的user_id字段数据类型不一致，BigQuery会执行隐式类型转换，可能出现你认为不匹配的ID被判定为匹配的情况：
比如表A的user_id是STRING类型存储的'00123'，表B的user_id是INT64类型存储的123，隐式转换后两个值会被判定为相等，导致原本属于A表独有的行被排除。
解决方法是显式统一关联字段的类型：

select a.user_id
from table_a a  
left outer join table_b b on cast(a.user_id as string) = cast(b.user_id as string)
where b.user_id is null

3. 关联字段存在隐形字符问题 #

如果user_id是字符串类型，可能存在前后空格、换行符、大小写差异等隐形差异，导致你肉眼认为相同的ID实际无法匹配。可以用清洗函数处理后再关联：

select a.user_id
from table_a a  
left outer join table_b b on lower(trim(a.user_id)) = lower(trim(b.user_id))
where b.user_id is null

替代写法推荐 #

可以使用NOT EXISTS语法实现相同逻辑，语法更简洁，也不容易出现过滤条件位置错误的问题，在BigQuery中的执行性能和左连接写法基本一致：

select a.user_id
from table_a a
where not exists (
  select 1 from table_b b
  where a.user_id = b.user_id
  -- 对b表的过滤条件直接写在子查询的WHERE中即可
)

快速验证方法 #

你可以先取一个确认仅存在于A表、不存在于B表的user_id，单独执行以下查询定位问题：

-- 确认A表存在该ID
select user_id from table_a where user_id = '待验证的ID' limit 1;
-- 确认B表不存在该ID
select user_id from table_b where user_id = '待验证的ID' limit 1;
-- 带入左连接查询看是否返回结果
select a.user_id
from table_a a  
left outer join table_b b on a.user_id = b.user_id
where a.user_id = '待验证的ID' and b.user_id is null;

内容的提问来源于stack exchange，提问作者crazy8