top of page

Outer Joins : which side does the (+) go on

  • Writer: kyle Hailey
    kyle Hailey
  • Mar 5, 2014
  • 1 min read

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.


typeANSIANSI 89 (Oracle)typetypeinner joinenglish INNER JOIN french using (ordinal_id)english e, french f  where e.ordinal_id=f.ordinal_id

left outer joinenglish LEFT JOIN french using (ordinal_id)english e, french f  where e.ordinal_id=f.ordinal_id(+)

right outer joinenglish RIGHT JOIN french using (ordinal_id)english e, french f  where e.ordinal_id(+)=f.ordinal_id

full joinenglish 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


 
 
 

Comentarios


Kyle Hailey

  • Facebook
  • Twitter
  • LinkedIn
  • Instagram

San Franisco, Ca 94131

415-341-3430  (please text initially before calling)

bottom of page