如何在BigQuery的UPDATE语句中实现LEFT JOIN？

SQL Server里可以直接通过LEFT JOIN关联表完成更新，示例写法：

UPDATE table1
SET table1.some_column = table2.some_column
FROM table1
LEFT JOIN table2 ON table1.some_value = table2.some_value
WHERE...

但BigQuery的常规UPDATE会默认将目标表与FROM子句的表做INNER JOIN，无法直接实现LEFT JOIN的逻辑——也就是SQL Server中table2无匹配行时会把table1对应字段设为NULL，而BigQuery常规UPDATE不会处理这类无匹配的行。

要在BigQuery中实现等价逻辑，有两种常用方案：

方法一：子查询+可选COALESCE #

通过关联子查询获取table2的匹配值，无匹配时返回NULL，以此覆盖table1的所有行：

UPDATE table1
SET some_column = (
  SELECT table2.some_column
  FROM table2
  WHERE table2.some_value = table1.some_value
)
-- 可添加WHERE条件过滤需要更新的行
WHERE ...

如果需要在无匹配时保留原字段值而非设为NULL，用COALESCE处理：

UPDATE table1
SET some_column = COALESCE(
  (SELECT table2.some_column FROM table2 WHERE table2.some_value = table1.some_value),
  table1.some_column
)
WHERE ...

方法二：使用MERGE语句 #

BigQuery的MERGE支持LEFT JOIN逻辑，可通过匹配分支处理更新，无匹配时也能指定动作：

MERGE INTO table1 AS t1
USING table2 AS t2
ON t1.some_value = t2.some_value
WHEN MATCHED THEN
  UPDATE SET t1.some_column = t2.some_column
WHEN NOT MATCHED BY SOURCE THEN
  -- 无匹配时若要设为NULL则保留此行，若要保留原值可省略该分支
  UPDATE SET t1.some_column = NULL
-- 可添加全局WHERE条件
WHERE ...

Dataform中的实现方式 #

Dataform支持直接编写BigQuery原生SQL，也可通过operations定义更新逻辑：

方式1：用operations配置MERGE #

operations({
  type: "merge",
  target: "table1",
  query: `
    SELECT some_value, some_column FROM table2
  `,
  whenMatched: `UPDATE SET some_column = source.some_column`,
  whenNotMatchedBySource: `UPDATE SET some_column = NULL` -- 根据需求选择是否保留
});

方式2：SQLX文件中写原生UPDATE #

UPDATE ${ref("table1")}
SET some_column = (
  SELECT some_column FROM ${ref("table2")} WHERE some_value = ${ref("table1")}.some_value
)
WHERE ...

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