Skip to content

Instantly share code, notes, and snippets.

@zjuul
Created August 23, 2018 13:58
Show Gist options
  • Save zjuul/aabde0ac52f3ee2974833cde718c56c6 to your computer and use it in GitHub Desktop.
Save zjuul/aabde0ac52f3ee2974833cde718c56c6 to your computer and use it in GitHub Desktop.
SQL joins: all in one
-- create two tables: L and R
-- content of tables a "val" column with two rows.
-- rows in L: "left only" and "both"
-- rows in R: "right only" and "both"
with l as (
select 'both' as val
union
select 'left_only' as val
), r as (
select 'both' as val
union
select 'right_only' as val
)
-- now do all kinds of joins and check the result.
select 'no join' as jointype,l.val as lval, r.val as rval from l,r
union
select 'join' as jointype,l.val as lval, r.val as rval from l join r on l.val = r.val
union
select 'left join' as jointype,l.val as lval, r.val as rval from l left join r on l.val = r.val
union
select 'right join' as jointype,l.val as lval, r.val as rval from l right join r on l.val = r.val
union
select 'full join' as jointype,l.val as lval, r.val as rval from l full join r on l.val = r.val
union
select 'inner join' as jointype,l.val as lval, r.val as rval from l inner join r on l.val = r.val
union
select 'plus left side' as jointype,l.val as lval, r.val as rval from r,l where l.val(+) = r.val
union
select 'plus right side' as jointype,l.val as lval, r.val as rval from l,r where l.val = r.val(+)
;
@zjuul
Copy link
Author

zjuul commented Aug 23, 2018

result

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment