在ON子句BigQuery Standard SQL中使用OR进行LEFT JOIN [英] LEFT JOIN with an OR in the ON clause BigQuery Standard SQL

问题描述

我需要一些帮助来了解bigquery标准sql中的联接.我想做一个左连接，将所有列保留在表1中，如果两个字段匹配或另一个2个字段匹配，则连接到表2.在我的示例中应该对此做更好的解释.

I need some help understanding joins in bigquery standard sql. I want to do a left join keeping all the columns in table1, and joining to table2 if 2 fields match OR a different 2 fields match. This should be better explained in my example.

表1:

id1 id2 column1  
1   a   first    
2   b   second   
3   c   third    
4   d   fourth   
5   e   fifth    
6   f   sixth

表2:

id3  id4    column2 
1    5674   alpha    
2    4535   bravo 
345  a      charlie  
341  b      delta

如果id1 = id3或id2 = id4，我想保留table1中的所有行以及table2中的所有行，结果表将如下所示:

I want to keep all the rows in table1 and all the rows from table2 if id1 = id3 OR id2 = id4, the resulting table would look like this:

结果:

id1 id2 column1 id3 id4     column2  
1   a   first   1   5674    alpha    
1   a   first   345 a       charlie  
2   b   second  2   4535    bravo    
2   b   second  341 b       delta
3   c   third    
4   d   fourth   
5   e   fifth    
6   f   sixth

尽管我无法获得此结果，因为似乎无法在ON子句中使用OR语句进行左联接.

Although I can't get this result as it seems I can't do a left join with an OR statement in the ON clause.

我已经尝试过以下查询:

I have tried this query:

SELECT * FROM table1 
JOIN table2
on (table1.id1 = table2.id3) or (table1.id2 = table2.id4)

这是一个内部联接，其结果为:

Which is an inner join and results in:

id1 id2 column1 id3 id4     column2  
1   a   first   1   5674    alpha    
1   a   first   345 a       charlie  
2   b   second  2   4535    bravo    
2   b   second  341 b       delta

几乎在那里，但不包括table1中的其他行

Which is almost there but does not include the other rows from table1

尝试此查询:

SELECT * FROM table1 
LEFT JOIN table2
on (table1.id1 = table2.id3) or (table1.id2 = table2.id4)

导致错误:

LEFT OUTER JOIN不能在没有以下条件的情况下使用 连接两端的字段相等.

LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join.

我了解可能有一个解决方法，我很想听听，但是也很高兴理解为什么内部联接的相同方法不适用于左联接.

I understand that there may be a workaround which I would love to hear, but also it would be good to understand why the same method for the inner join is not applicable to the left join.

预先感谢， 瑞安

编辑 我仍在努力弄清为什么不可能做到这一点，Mikhail的解决方法很好地占用了很多资源，我想了解是什么阻止了我使用条件左联接.

EDIT I am still struggling to get my head around why this is not possible, Mikhail's workaround is fine quite heavy on resources and I'd like to understand what is preventing me from using the conditional left join.

推荐答案

以下是BigQuery标准SQL

Below is for BigQuery Standard SQL

其中一个选项:

#standardSQL
SELECT DISTINCT * FROM (
  SELECT * FROM `project.dataset.table1` 
  LEFT JOIN `project.dataset.table2` 
  ON id1 = id3   
  UNION ALL
  SELECT * FROM `project.dataset.table1` 
  LEFT JOIN `project.dataset.table2` 
  ON id2 = id4 
)

这篇关于在ON子句BigQuery Standard SQL中使用OR进行LEFT JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！