Outer Joins Graphically

December 30th, 2009
Outer Joins

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.


idea for the above graphic came from http://blog.mclaughlinsoftware.com/wp-content/uploads/2009/02/joinmapping.png which was originally posted 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 Query Examples


Uncategorized

  1. Trackbacks

  2. October 23rd, 2011: Tuning Blog Entries « Ukrainian Oracle User Group
  1. Comments

  2. No comments yet.


eight − 3 =