BigQuery - SQL joins: all in one

bq_sql_joins_demo.sql

-- zjuul wrote: https://gist.github.com/zjuul/aabde0ac52f3ee2974833cde718c56c6 
-- I converted to BigQuery syntax:
with l as (
	 select  'both'  as val
	union all
	 select 'left_only' as val
), r as (
	 select 'both' as val
	union all
	 select 'right_only' as val
)
-- now do all kinds of joins and check the result.
select * from (
 select 1 as row, 'no join' as jointype,l.val as lval, r.val as rval from l,r
union all
 select 2 as row, 'join' as jointype,l.val as lval, r.val as rval from l join r on l.val = r.val
union all
 select 3 as row, 'left join' as jointype,l.val as lval, r.val as rval from l left join r on l.val = r.val
union all 
 select 4 as row, 'right join' as jointype,l.val as lval, r.val as rval from l right join r on l.val = r.val
union all 
 select 5 as row, 'full join' as jointype,l.val as lval, r.val as rval from l full join r on l.val = r.val
union all
 select 6 as row, 'inner join' as jointype,l.val as lval, r.val as rval from l inner join r on l.val = r.val
union all
 select 7 as row, 'outter left side' as jointype,l.val as lval, r.val as rval from l  left outer join r on  l.val = r.val
union all
 select 8 as row, 'outter right side' as jointype,l.val as lval, r.val as rval from l right outer join r on   l.val = r.val
) 
order by row asc