Data Virtualizer

Bobby Durrett just put together a great little post on “outer joins : where do I put the (+)“.

I also have hard time remembering, and below is my cheat sheet using graphics.

If English and French both have a unique key on the “ordinal_id” then it’s basically one-to-one relationship

We add an arrow in the middle of the line to denote “outer join”. The arrow points from the table that drives the join, ie all the rows in the table pointed from are returned even if a match isn’t found in the table pointed to.

            

above graphic originally on http://blog.mclaughlinsoftware.com/oracle-sql-programming/basic-sql-join-semantics/

type	ANSI	ANSI 89 (Oracle)	type	type
inner join	english INNER JOIN french using (ordinal_id)	english e, french f  where e.ordinal_id=f.ordinal_id
left outer join	english LEFT JOIN french using (ordinal_id)	english e, french f  where e.ordinal_id=f.ordinal_id(+)
right outer join	english RIGHT JOIN french using (ordinal_id)	english e, french f  where e.ordinal_id(+)=f.ordinal_id
full join	english FULL JOIN french using (ordinal_id)	english e, french f where e.ordinal_id=f.ordinal_id(+) UNION english e, french f where e.ordinal_id(+)=f.ordinal_id

     

outer join example sql

khailey
Uncategorized