BigQuery中实现Full Join并确保左表列始终填充（含Null）

问题根源 #

你用Full Join踩的坑很典型：Full Join会把两张表里所有不匹配的行都拉进来，既会引入country表中跟任何人员都无关的大洲数据，还会导致Name字段在country表无匹配的行里空着——这完全不符合你的需求。正确的做法是以people表为核心，用LEFT JOIN来关联country表，只保留和人员匹配的记录，同时自动保留people里Country为Null的行。

可行SQL方案 #

通用版（兼容country表存在重复国家-大洲的情况） #

SELECT
  p.Name,
  p.Country AS people_country,
  p.Continent AS people_continent,
  c.Country AS country_country,
  c.Continent AS country_continent
FROM
  `你的项目ID.你的数据集ID.people` p
LEFT JOIN
  `你的项目ID.你的数据集ID.country` c
ON
  -- 匹配同国家同大洲的记录，同时保留people中Country为Null的行
  (p.Country IS NOT NULL AND c.Country = p.Country AND c.Continent = p.Continent)
  OR p.Country IS NULL

简化版（如果country表中Country是唯一键，每个国家对应唯一大洲） #

如果country表的Country字段不会重复，每个国家只对应一个大洲，可以简化匹配逻辑，LEFT JOIN会自动保留people表的所有行，包括Country为Null的：

SELECT
  p.Name,
  p.Country,
  p.Continent,
  c.Country,
  c.Continent
FROM
  `你的项目ID.你的数据集ID.people` p
LEFT JOIN
  `你的项目ID.你的数据集ID.country` c
ON
  p.Country = c.Country
  -- 额外加大洲匹配，避免极端情况下同国家不同大洲的错误关联
  AND p.Continent = c.Continent

示例验证 #

假设两张表的测试数据：

people表

country表

执行简化版SQL后，得到的结果是：

这个结果完全符合需求：

• 所有人员行都被保留，包括Bob的Null行
• 没有引入country表里无关的India记录
• Name字段全程有值，不会出现空的情况

关键要点 #

• LEFT JOIN是核心：它保证主表（people）的所有行都被保留，不管在关联表（country）里有没有匹配项
• 同时匹配Country和Continent是必要的：避免出现同国家但大洲不一致的错误关联（如果你的数据里有这种情况）
• 别用Full Join：它会把country表中所有不相关的记录也带进来，完全不是你要的结果

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