BigQuery在JOIN ON子句用子查询报Unsupported subquery with table in join predicate错误

BigQuery 不支持 在JOIN语句的ON子句中使用带表引用的子查询，你遇到的报错就是该语法限制直接导致的。

BigQuery的JOIN ON谓词仅支持常量、关联双方表的字段运算结果、无表引用的标量函数结果，不允许嵌套直接引用其他表的子查询。你提供的示例代码中，ON条件内的子查询引用了server.dataset.table表，不符合语法规则。

• 方案1：预计算子查询结果再关联
  先用CTE预计算出全局最大账号值，再在关联条件中引用该常量值，改写后代码如下：

  WITH max_account AS (
    SELECT MAX(m.AccountNumber) AS max_num 
    FROM `server.dataset.table` m
  )
  SELECT '1'
  FROM `server.dataset.table` a
  JOIN `server.dataset.table` b
  ON a.AccountNumber = (SELECT max_num FROM max_account)
  

• 方案2：子查询逻辑移至WHERE子句
  对于无关联行依赖的标量子查询，可以直接将判断逻辑从ON子句移到WHERE子句，语义和原SQL完全一致：

  SELECT '1'
  FROM `server.dataset.table` a
  JOIN `server.dataset.table` b
  WHERE a.AccountNumber = (SELECT MAX(m.AccountNumber) FROM `server.dataset.table` m)
  

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