#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2006
    Posts
    44
    Rep Power
    0

    Differances between INNER JOIN and OUTER JOIN


    I was curious as to the situations when you would use INNER JOIN over OUTER JOIN and OUTER JOIN over INNER JOIN and what data LEFT JOIN would pull? I am currently using INNER JOIN to join 2 tables for relational reasons but cant really figure out OUTER JOIN so thought I should ask.

    Thanks
  2. #2
  3. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,993
    Rep Power
    9397
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,139
    Rep Power
    4274
    Originally Posted by requinix
    which was written by guys who likely know what they're doing but can't splain it clearly to save their lifes

    a lot of people like this visual representation -- http://mxtutorial.com/2011/05/visual-sql-joins/

    of course, i have a fond attachment to my explanation in this old thread on sitepoint, which eventually made it into my book -- http://www.sitepoint.com/forums/show...2&postcount=18
    Last edited by r937; August 2nd, 2011 at 02:26 PM.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2006
    Posts
    44
    Rep Power
    0
    So the full outer join is saying everything that doesn't match from table a and table b as in your example?
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2006
    Posts
    44
    Rep Power
    0
    oh, that would only be the case if we did a where clause for NULL?
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,139
    Rep Power
    4274
    Originally Posted by reaper7861
    oh, that would only be the case if we did a where clause for NULL?
    yeah, sort of

    see, the diagrams are also hard to grasp at first, aren't they

    also, notice there is no diagram for cross join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2006
    Posts
    44
    Rep Power
    0
    Yes i noticed that and didn't even know that cross joining existed, I am curious as to what you would use that for?
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,139
    Rep Power
    4274
    you would use a cross join to generate all possible combinations of pairs of values

    just remember that, and you will recognize the opportunity when it arises -- which isn't often, admittedly, but the cross join can be very useful
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2006
    Posts
    44
    Rep Power
    0
    That is great information, thanks a lot.

IMN logo majestic logo threadwatch logo seochat tools logo