Bigquery Full Join ON multiple conditions

Question

I want to perform a Full Outer join on two tables with multiple conditions to result all the matching records along with unmatched records from both tables. Tbl1 is a bigger table with 21 M records and Tbl2 has 5k rows, like the example query below. But the outer join cannot be performed with OR conditions because of the error 'FULL OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join'. Is writing separate queries, then use COALESCE the only solution in this case? I am not sure how to implement this solution. Looking for any help to get this correct.

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)

Example code -

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

Please provide sample data and desired results. You do not need all 20 columns to convey the idea of what you need. Also, a clear explanation of the logic you want to implement would help. — Gordon Linoff
What is the last table? Your desired result? You don't say how the desired result is a function of input or even clelarly give input & desired output for us to guess. Why do you expect us to be able to help you write a query to do that? (Rhetorical.) minimal reproducible example See How to Ask, other help center links & the voting arrow mouseover texts. PS Please before you post look at the formatted version of your post below the edit box. — philipxy
Learn what OUTER JOINs return: LEFT/RIGHT JOIN ON return INNER JOIN ON rows UNION ALL unmatched left/right table rows extended by NULLs. FULL JOIN ON returns INNER JOIN ON rows UNION ALL unmatched left & right table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. After a LEFT or RIGHT JOIN ON, a WHERE or INNER JOIN ON that requires a column with introduced NULL to be not NULL removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". Similarly for FULL JOIN ON. You have that. And it is faq. — philipxy

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2019-12-06T21:25:56

Just move your conditions from ON to WHERE and optionally optimize all those ORs as in below

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)

so you final query can look like below

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)

Bigquery Full Join ON multiple conditions

1 Answers