BigQuery中按子串匹配关联表、未匹配行单独返回实现方法

核心逻辑 #

需求本质是**全外连接（FULL OUTER JOIN）**场景：匹配规则为ID是productCode的子串，两张表所有未匹配的记录都独立保留，缺失字段自动填充空值，和预期输出的空单元格效果一致。
子串判断直接使用BigQuery原生CONTAINS_SUBSTR(目标字符串, 查找子串)函数，无需手动拼接LIKE通配符，语法简洁且性能更好。

完整实现代码 #

以下代码包含样例测试数据，实际使用时把CTE部分替换成自己的业务表即可直接运行：

WITH
-- ID价格表 替换为实际表名
id_price AS (
  SELECT '07a' AS ID, 50 AS price UNION ALL
  SELECT '1b7' AS ID, 60 AS price UNION ALL
  SELECT '7TC' AS ID, 40 AS price
),
-- 产品信息表 替换为实际表名
product_info AS (
  SELECT 'newhair_07a' AS productCode, 'detailA' AS details UNION ALL
  SELECT 'black_1b7_chair' AS productCode, 'detailB' AS details UNION ALL
  SELECT 'blueBed' AS productCode, 'detailC' AS details
)
SELECT
  p.ID,
  pr.productCode,
  pr.details,
  p.price
FROM id_price p
FULL OUTER JOIN product_info pr
  ON CONTAINS_SUBSTR(pr.productCode, p.ID)

结果说明 #

执行后返回结果和预期完全一致：

• 匹配成功的07a、1b7两条记录，会关联返回对应产品编码、详情、价格
• 产品表未匹配的blueBed记录，ID、price字段为空
• 价格表未匹配的7TC记录，productCode、details字段为空

补充说明 #

• 若业务上匹配不需要区分大小写，可将连接条件调整为ON CONTAINS_SUBSTR(UPPER(pr.productCode), UPPER(p.ID))，统一转大写后匹配，避免大小写差异导致漏匹配
• 若存在一个productCode包含多个ID的场景，该写法会自动返回所有符合条件的匹配行，符合关联逻辑
• 禁止使用左连接/右连接实现，这两种连接方式只能保留单边表的未匹配记录，无法满足双向保留的要求

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