如何在BigQuery中编写多表连接？Oracle转BigQuery SQL求助

问题背景 #

需要将使用Oracle旧版(+)右连接语法的查询转换为BigQuery支持的SQL，原Oracle查询如下：

SELECT * FROM 
t1,t2,t3,t4,t5,t6,t7,t8,t9,t10
WHERE 
    t1.CODE (+) = t2.CODE
AND t1.BRANCH_NBR (+) = t2.BRANCH_NBR
AND t1.NBR (+) = t2.NBR
AND t1.DT (+) = t2.DT

AND t3.COMPANY (+) = t4.CODE
AND t3.INV_PART_NBR (+) = t4.PART_NBR
AND t5.COMPANY (+) = t3.COMPANY
AND t5.CLIENT_NBR (+) = t3.CLIENT_NBR
AND t5.SEQ_NBR (+) = t3.SEQ_NBR
AND t6.COMPANY (+) = t3.COMPANY
AND t6.CLIENT_NBR (+) = t3.CLIENT_NBR
AND t6.SEQ_NBR (+) = t3.SEQ_NBR

AND t7.COMPANY (+) = t8.COMPANY
AND t7.CLIENT_NBR (+) = t8.MV_NUMBER
AND t7.VENDOR_CLASS (+) = 'A'
AND t9.COMPANY (+) = t2.CODE
AND t9.BR_NBR (+) = t2.BB_NBR
AND t9.CUST_NBR (+) = t2.BC_NBR
AND t9.SUFFIX (+) = t2.SHIP_TO_SFX
AND t10.COMPANY (+) = t2.CODE
AND t10.BR_NBR (+) = t2.BB_NBR
AND t10.CUST_NBR (+) = t2.BC_NBR
AND t10.SUFFIX (+) = '000'

尝试转换时使用RIGHT OUTER JOIN出现语法错误，示例代码：

SELECT *
FROM
`t1` AS t1
RIGHT OUTER JOIN `t2` AS t2 USING (CODE,BRANCH_NBR,NMBR,DT),
`t3` AS t3
RIGHT OUTER JOIN 't4' AS t4 USING (CODE,PART_NBR,COMPANY)

错误提示：

语法错误：在逗号连接后使用RIGHT JOIN必须加括号。此外，如果前面的逗号连接是关联CROSS JOIN并展开数组，则必须使用CROSS JOIN语法替代逗号连接

错误原因 #

BigQuery不支持混合使用逗号（隐式交叉连接）和显式RIGHT/LEFT JOIN的写法，必须统一使用显式连接语法，且多表连接需要明确层级或用括号分组。

转换规则 #

Oracle的a.col (+) = b.col等价于b RIGHT OUTER JOIN a ON a.col = b.col，需要把每一组(+)连接转换为对应的显式右连接，同时所有表连接都用显式语法替代逗号。

完整转换后的BigQuery SQL #

SELECT *
FROM t2
RIGHT OUTER JOIN t1 
  ON t1.CODE = t2.CODE
  AND t1.BRANCH_NBR = t2.BRANCH_NBR
  AND t1.NBR = t2.NBR
  AND t1.DT = t2.DT
CROSS JOIN t4
RIGHT OUTER JOIN t3 
  ON t3.COMPANY = t4.CODE
  AND t3.INV_PART_NBR = t4.PART_NBR
RIGHT OUTER JOIN t5 
  ON t5.COMPANY = t3.COMPANY
  AND t5.CLIENT_NBR = t3.CLIENT_NBR
  AND t5.SEQ_NBR = t3.SEQ_NBR
RIGHT OUTER JOIN t6 
  ON t6.COMPANY = t3.COMPANY
  AND t6.CLIENT_NBR = t3.CLIENT_NBR
  AND t6.SEQ_NBR = t3.SEQ_NBR
CROSS JOIN t8
RIGHT OUTER JOIN t7 
  ON t7.COMPANY = t8.COMPANY
  AND t7.CLIENT_NBR = t8.MV_NUMBER
  AND t7.VENDOR_CLASS = 'A'
RIGHT OUTER JOIN t9 
  ON t9.COMPANY = t2.CODE
  AND t9.BR_NBR = t2.BB_NBR
  AND t9.CUST_NBR = t2.BC_NBR
  AND t9.SUFFIX = t2.SHIP_TO_SFX
RIGHT OUTER JOIN t10 
  ON t10.COMPANY = t2.CODE
  AND t10.BR_NBR = t2.BB_NBR
  AND t10.CUST_NBR = t2.BC_NBR
  AND t10.SUFFIX = '000'

关键说明 #

1. 原Oracle查询中未关联的表组（如t2/t1、t4/t3/t5/t6、t8/t7、t2/t9/t10）之间是隐式交叉连接，转换为BigQuery时用CROSS JOIN显式声明。
2. 所有(+)右连接都转换为RIGHT OUTER JOIN，并将连接条件移至ON子句中。
3. 避免混合使用逗号和显式连接，统一用显式连接语法消除语法错误。

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