CODEWITHSUNDEEP
google-bigquery
Nils Ziehn
Nils Ziehn

Reputation: 4331

LEFT JOIN EACH in Bigquery loses rows

I have three tables: a, b, c

For simplicity in this example, they all have only one column, an id column. These ids are NEVER NULL!

Now I join all three tables:

SELECT * FROM 
a
LEFT JOIN EACH b
ON a.id = b.id
LEFT JOIN EACH c
ON b.id = a.id

Now the problem:

b does not contain all 'ids' from a, therefore some NULL-values occur, when I then join c, the rows with these NULL-values are removed! From my understanding, LEFT JOIN should never remove rows, but only add new ones, right?

Example:

a: id = [1,2]
b: id = [1]
c: id = [1]

Bigquery would return 

row1: 1,1,1

My expected result:

row1: 1,1,1
row4: 2,NULL, NULL

Is there a way to get the expected behaviour in BigQuery?

UPDATE:

Thx to @Felipe and @oulenz for the input, i've now tested:

select *
from (select 1 as id) a
left outer join (select 2 as id) b
  on b.id = a.id
left outer join (select 2 as id) c
  on c.id = b.id

And this yields my expected result 1,null,null and does not reproduce my problem.

I will have to investigate further where the problem is. Thx for your input so far!

Upvotes: 0

Views: 3276

Answers (2)

Nicholas
Nicholas

Reputation: 1714

This question is slightly dated. At the time, this question reflected an issue with how the BigQuery SQL query was translated internally. This issue was tracked publicly on the BigQuery public issue tracker issue 35905407 (migrated from Google Code to Issue Tracker) and has since been fixed.

Use of EACH is no longer recommended and a part of Legacy SQL from which Big Query is moving away since adopting Stadard SQL. If this issue still affects you, I'd recommend removing the EACH modifier as mentioned in previous answers/comments or consider rewriting the query to conform to Standard SQL.

Upvotes: 1

Felipe Hoffa
Felipe Hoffa

Reputation: 59175

I tested what you ask for, and it works as you expected it to work:

SELECT a.x, b.x, c.x
FROM (
  SELECT x FROM (SELECT 1 x), (SELECT 2 x)
) a
LEFT JOIN (
  SELECT 1 x
) b
ON a.x=b.x
LEFT JOIN (
  SELECT 1 x
) c
ON a.x=c.x

enter image description here

Upvotes: 0

Related Questions