Tuesday, December 29, 2009

Outer Joins Graphically

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/
    typeANSIANSI 89 (Oracle)typetype
    inner 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 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

No comments:

Post a Comment

Post a Comment