Difference between outputs of standard and legacy SQL in Google BigQuery

Asked Dec 05 '17 at 10:05

Active Dec 05 '17 at 13:01

Viewed 124 times

I have a query written in legacy SQL mode which I want to rewrite into StandardSQL. It has a few inner joins, and standardSQL has a few places where I have to unnest data using UNNEST functions (arrays of product IDs inside a row of transactionID).

However, after rewriting and comparing outputs I noticed that the one in standardSQL has slightly fewer rows (194310 vs 193907).

Is there any general reason why this happens, and how to avoid it?

edited Dec 05 '17 at 13:01

asked Dec 05 '17 at 10:05

Azamat

Slightly different?! It's 174,920 off. Or is that a typo? I'd suspect you made an mistake in your port to standard somewhere along the line. – Graham Polley Dec 05 '17 at 12:45
@GrahamPolley a typo, forgot one digit in second number – Azamat Dec 05 '17 at 13:01
You might want LEFT JOIN instead of INNER JOIN. Please share the queries themselves or examples. – Elliott Brossard Dec 05 '17 at 14:32

Difference between outputs of standard and legacy SQL in Google BigQuery

0 Answers0