Big Query中如何实现一对多关联全外连接？遇内连接结果问题

核心问题分析 #

Full Outer Join本身会保留左右两表的所有行，但如果你的查询存在WHERE子句过滤空值或关联条件设置不当，会导致外层连接生成的空值行被过滤，最终结果和Inner Join一致。另外，若两张表的关键词本身完全匹配，Full Outer Join的结果自然和Inner Join无差异。

具体解决方案 #

• 把过滤条件移到ON子句而非WHERE子句
  若原查询在WHERE中对关联表的列做非空判断，会直接过滤掉左/右表无匹配的行。将这类条件放到JOIN的ON子句中，既能过滤关联数据，又能保留外层连接的所有行。
  错误示例：

  SELECT t1.rank_keyword, t2.matched_content
  FROM table_rank t1
  FULL OUTER JOIN table_match t2 ON t1.keyword = t2.keyword
  WHERE t2.matched_content IS NOT NULL;
  

  正确示例：

  SELECT t1.rank_keyword, t2.matched_content
  FROM table_rank t1
  FULL OUTER JOIN table_match t2 
    ON t1.keyword = t2.keyword
    AND t2.matched_content IS NOT NULL;
  

• 统一关键词格式后再关联
  如果关键词存在大小写、空格、特殊字符差异，会导致匹配失败。先统一格式再执行连接：

  SELECT t1.rank_keyword, t2.matched_content
  FROM table_rank t1
  FULL OUTER JOIN table_match t2 
    ON LOWER(TRIM(t1.keyword)) = LOWER(TRIM(t2.keyword));
  

• 用UNION ALL组合左右连接模拟Full Outer Join
  部分数据库对Full Outer Join支持有限，可通过LEFT JOIN + RIGHT JOIN + UNION ALL实现相同效果，确保保留所有行及一对多的重复值：

  -- 保留左表所有行及对应匹配数据
  SELECT t1.rank_keyword, t2.matched_content
  FROM table_rank t1
  LEFT JOIN table_match t2 ON t1.keyword = t2.keyword
  UNION ALL
  -- 补充右表中未被左表匹配的行
  SELECT t1.rank_keyword, t2.matched_content
  FROM table_rank t1
  RIGHT JOIN table_match t2 ON t1.keyword = t2.keyword
  WHERE t1.rank_keyword IS NULL;
  

• 验证数据本身的匹配情况
  先确认两张表是否真的存在无匹配的关键词，若所有关键词都互相匹配，Full Outer Join结果自然和Inner Join一致：

  -- 检查左表独有关键词
  SELECT rank_keyword FROM table_rank
  WHERE keyword NOT IN (SELECT keyword FROM table_match);
  
  -- 检查右表独有关键词
  SELECT keyword FROM table_match
  WHERE keyword NOT IN (SELECT keyword FROM table_rank);
  

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