BigQuery简单LEFT JOIN未保留左表行问题排查

问题根源 #

1. LEFT JOIN结果被WHERE条件意外过滤：原查询中WHERE DATE(Lines._PARTITIONTIME) >= "2023-02-01"会直接剔除LEFT JOIN后Lines表字段全为NULL的行——因为NULL与日期比较的结果为UNKNOWN，WHERE仅保留结果为TRUE的行，所以你只能拿到有LineItems数据的2条记录。
2. 强制分区查询的规则限制：你尝试添加Lines._PARTITIONTIME IS NULL的条件后触发报错，是因为BigQuery的强制分区查询配置要求必须提供能用于分区消除的过滤条件，而IS NULL无法帮助BigQuery定位具体分区，因此不符合规则。

修正后的查询语句 #

将Lines表的分区过滤条件从WHERE子句移到LEFT JOIN的ON子句中，既能保留左表（Plants+PlantDescriptions）的所有数据，又能满足分区过滤要求：

SELECT DISTINCT
  P.name AS `plantName`,
  P.sku AS `plantSku`,
FROM
  `wow-plants-for-sale.ds1.Plants` AS P
JOIN
  `wow-plants-for-sale.ds1.PlantDescriptions` AS D ON P.sku = D.sku
LEFT JOIN 
  `wow-plants-for-sale.ds1.LineItems` AS Lines 
  ON D.sku = Lines.sku 
  AND DATE(Lines._PARTITIONTIME) >= "2023-02-01" -- 把分区过滤移至此处
WHERE
  DATE(P._PARTITIONTIME) >= "2023-02-01"
  AND DATE(D._PARTITIONTIME) >= "2023-02-01"

方案有效性说明 #

• 把分区过滤放在LEFT JOIN的ON子句中，只会筛选Lines表中符合分区条件的记录进行关联，不会过滤左表中没有匹配Lines的行（这些行的Lines字段依然为NULL）。
• ON子句中的DATE(Lines._PARTITIONTIME) >= "2023-02-01"是有效的分区消除条件，符合BigQuery强制分区查询的规则，不会触发报错。

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