How to do a partitioned outer join in BigQuery

Question

I would like to implement the partitioned outer join in BigQuery. To give a concrete example, I'd like to achieve the partitioned outer join as the accepted answer here: https://dba.stackexchange.com/questions/227069/what-is-a-partitioned-outer-join

I understand there are a lot of discussions about this topic, but I can't make it work under BigQuery. I added partition by date after the left table following the same syntax in the answer as follows:

select * from ( 
 select '2019-01-17' as date, 'London' as location, 11 as qty   
 union all   
 select '2019-01-15' as date, 'London' as location, 10 as qty   
 union all   
 select '2019-01-16' as date, 'Paris' as location, 20 as qty   
 union all   
 select '2019-01-17' as date, 'Boston' as location, 31 as qty   
 union all   
 select '2019-01-16' as date, 'Boston' as location, 30 as qty 
) as sales partition by (date)  
right join 
(   
 select 'London' as location   
 union all   
 select 'Paris' as location   
 union all   
 select 'Boston' as location   
) 
as loc 
using (location)

The target result I'm looking for is:

date      qty    location  
15-JAN-19  NULL  Boston  
15-JAN-19  10    London  
15-JAN-19  NULL  Paris   
16-JAN-19  30    Boston  
16-JAN-19  NULL  London  
16-JAN-19  20    Paris   
17-JAN-19  31    Boston  
17-JAN-19  11    London  
17-JAN-19  NULL  Paris

But I got the following error: Syntax error: Unexpected keyword PARTITION at [11:12]

How can I implement it in BigQuery?

"but I can't make it work under BigQuery." - can you be more specific? what you tried and how it did not work for you? please elaborate — Mikhail Berlyant
I added an example in OP to show the issue I have. Thank you! — user2830451

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2019-10-10T21:52:32

Below is for BigQuery Standard SQL

#standardSQL
SELECT `date`, qty, location 
FROM (SELECT DISTINCT `date` FROM sales)
CROSS JOIN loc 
LEFT JOIN sales
USING (`date`, location)

You can test, play with above using sample data from your question as in below example

#standardSQL
WITH sales AS (
 SELECT '2019-01-17' AS `date`, 'London' AS location, 11 AS qty UNION ALL   
 SELECT '2019-01-15', 'London', 10 UNION ALL   
 SELECT '2019-01-16', 'Paris', 20 UNION ALL   
 SELECT '2019-01-17', 'Boston', 31 UNION ALL   
 SELECT '2019-01-16', 'Boston', 30 
), loc AS (   
 SELECT 'London' AS location UNION ALL   
 SELECT 'Paris' UNION ALL   
 SELECT 'Boston' 
) 
SELECT `date`, qty, location 
FROM (SELECT DISTINCT `date` FROM sales)
CROSS JOIN loc 
LEFT JOIN sales
USING (`date`, location)
-- ORDER BY `date`, location

with below result

Row date        qty     location     
1   2019-01-15  null    Boston   
2   2019-01-15  10      London   
3   2019-01-15  null    Paris    
4   2019-01-16  30      Boston   
5   2019-01-16  null    London   
6   2019-01-16  20      Paris    
7   2019-01-17  31      Boston   
8   2019-01-17  11      London   
9   2019-01-17  null    Paris

In case if you need dates to be in 15-JAN-19 format - you below

#standardSQL
SELECT FORMAT_DATE('%d-%b-%y', CAST(`date` AS DATE)) AS `date`, qty, location 
FROM (SELECT DISTINCT `date` FROM sales)
CROSS JOIN loc 
LEFT JOIN sales
USING (`date`, location)

so result will be

Row date        qty     location     
1   15-Jan-19   null    Boston   
2   15-Jan-19   10      London   
3   15-Jan-19   null    Paris    
4   16-Jan-19   30      Boston   
5   16-Jan-19   null    London   
6   16-Jan-19   20      Paris    
7   17-Jan-19   31      Boston   
8   17-Jan-19   11      London   
9   17-Jan-19   null    Paris

How to do a partitioned outer join in BigQuery

1 Answers