sql - Bigquery Full Join ON 多个条件

Question

我想对具有多个条件的两个表执行完全外部联接，以生成所有匹配记录以及两个表中不匹配的记录。 Tbl1 是一个更大的表，有 21 M 条记录，Tbl2 有 5k 行，如下面的示例查询。但是由于错误“FULL OUTER JOIN cannot be used without a condition that is equality of fields from both sides of join”，因此无法使用 OR 条件执行外部联接。编写单独的查询，然后在这种情况下使用 COALESCE 是唯一的解决方案吗？我不确定如何实现此解决方案。寻找任何帮助来解决这个问题。

SELECT  
* 
FROM 
`myproject.table1` as t1
Full Outer JOIN
`myproject.table2` as t2
ON
(
t1.Camp1ID = t2.ID
OR t1.Camp2ID = t2.ID
OR t1.Camp3ID = t2.ID
OR t1.Camp4ID = t2.ID
OR t1.Camp5ID h = t2.ID
OR t1.Camp6ID = t2.ID
OR t1.Camp7ID = t2.ID
OR t1.Camp8ID = t2.ID
OR t1.Camp9ID = t2.ID
OR t1.Camp10ID = t2.ID
OR t1.Camp11ID = t2.ID
OR t1.Camp12ID = t2.ID
OR t1.Camp13ID = t2.ID
OR t1.Camp14ID = t2.ID
OR t1.Camp15ID = t2.ID
OR t1.Camp16ID = t2.ID
)
Where
t1.Date BETWEEN  PARSE_DATE('%m/%d/%y', t2.StartDate) AND  PARSE_DATE('%m/%d/%y', t2.EndDate)

示例代码-

Tbl1:

EmpNo   EmpITPrj    EmpFinPrj   EmpHRPrj    EmpIntPrj           Date
1           IT101       null            null         null           2019-09-01
2            null        Fin101         null         null            2001-06-05
3            null        null           HR101    null           2005-11-25
4           null        null            null         Int501     2010-10-15
5           null        null            null         Int105     2019-01-10

Tbl2:

PrjID   PrjStartDate    PrjEndDate
Fin101  06/01/2005      08/14/2005
IT102   07/11/2006      10/30/2006
Int105   09/15/2019      10/01/2019

EmpNo   EmpITPrj    EmpFinPrj   EmpHRPrj    EmpIntPrj   Date            PrjID    PrjStartDate           PrjEndDate
1           IT101       null            null            null         2019-09-01     null            null                null        
2            null        Fin101         null            null         2001-06-05     Fin101          06/01/2005          08/14/2005
3           null        null            HR101        null        2005-11-25     null            null                null
4           null        null            null            Int501   2010-10-15     null            null                null
5           null        null            null            Int105   2019-01-10     Int105          09/15/2019          10/01/2019
null        null        null        null        null         null           IT102       07/11/2006          10/30/2006

Answer 1

只需将您的条件从 ON 移动到 WHERE 并有选择地优化所有这些 OR，如下所示

t2.ID IN (t1.Camp1ID,t1.Camp2ID,t1.Camp3ID,t1.Camp4ID,t1.Camp5ID,t1.Camp6ID,t1.Camp7ID,t1.Camp8ID,t1.Camp9ID,t1.Camp10ID,t1.Camp11ID,t1.Camp12ID,t1.Camp13ID,t1.Camp14ID,t1.Camp15ID,t1.Camp16ID)  

所以你最终的查询可以如下所示

SELECT * 
FROM `myproject.table1` as t1
Full Outer JOIN `myproject.table2` as t2
ON TRUE
Where t1.Date BETWEEN  PARSE_DATE('%m/%d/%y', t2.StartDate) AND  PARSE_DATE('%m/%d/%y', t2.EndDate)
And t2.ID IN (t1.Camp1ID,t1.Camp2ID,t1.Camp3ID,t1.Camp4ID,t1.Camp5ID,t1.Camp6ID,t1.Camp7ID,t1.Camp8ID,t1.Camp9ID,t1.Camp10ID,t1.Camp11ID,t1.Camp12ID,t1.Camp13ID,t1.Camp14ID,t1.Camp15ID,t1.Camp16ID)