SQL joins: all in one

all_join_types.sql

-- 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(+)
;