Page 1 of 2 12 LastLast
Results 1 to 15 of 21

Thread: SQL join types

  1. #1
    Join Date
    Aug 2008
    Location
    Australia
    Posts
    23

    SQL join types

    I believe that I have found the following SQL join "commands" ("command" is not the correct term I believe. What is (keyword??)?) to exist;
    JOIN
    CROSS JOIN
    INNER JOIN
    OUTER JOIN
    NATURAL JOIN
    LEFT OUTER JOIN
    RIGHT OUTER JOIN
    FULL OUTER JOIN

    When using the qualifiers LEFT, RIGHT or FULL can you leave the qualifier OUTER out as it is implied?

    Is this list complete or are there other joins possible? Are there errors in my list? Please add to, delete or edit my list as appropriate.

    What type of join is JOIN?
    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Are you looking for ANSI SQL answers?

    For all implementations I know:
    JOIN == INNER JOIN
    LEFT\RIGHT\FULL JOIN == LEFT\RIGHT\FULL OUTER JOIN
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in some versions of mysql, JOIN == CROSS JOIN, and in others, INNER JOIN == CROSS JOIN if no ON clause is specified

    both of the above are non-standard

    poots was correct, there are only these types of join:

    CROSS JOIN
    [NATURAL] INNER JOIN
    [NATURAL] LEFT [OUTER] JOIN
    [NATURAL] RIGHT [OUTER] JOIN
    [NATURAL] FULL [OUTER] JOIN

    NATURAL means no ON clause is used, but almost everyone advises against using NATURAL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Aug 2008
    Location
    Australia
    Posts
    23
    Thanks a lot for your time. My experiments have yielded the following;

    ** The following are both equivalent in their effect. They both result in a cartesian multiplication of two relations. They can only be used without an ON clause;
    ,
    CROSS JOIN

    ** NATURAL joins look for attributes in each relation with the same name and type and join on this common attribute. If there is a common attribute, NATURAL joins then only join rows where a common value in the common attribute exists. If no commonly named and typed attributes exist, the NATURAL join performs a cartesian multiplication of the two relations. NATURAL joins cannot be used in conjunction with an ON clause. These are the possible NATURAL join variants;
    NATURAL JOIN
    NATURAL INNER JOIN
    NATURAL LEFT JOIN
    NATURAL LEFT OUTER JOIN
    NATURAL RIGHT JOIN
    NATURAL RIGHT OUTER JOIN
    NATURAL FULL JOIN
    NATURAL FULL OUTER JOIN
    (I haven't yet worked out what happens if there are two or more common attributes)

    ** The following clauses must be used in combination with an ON clause. These clauses have equivalents as detailed;
    LEFT JOIN == LEFT OUTER JOIN
    RIGHT JOIN == RIGHT OUTER JOIN
    FULL JOIN == FULL OUTER JOIN
    JOIN == INNER JOIN

    ** The clauses JOIN and INNER JOIN cannot be combined with the clauses RIGHT, LEFT or FULL.

    ** It seems the OUTER clause is totally redundant. However if it is used it must be used in combination with one of the following clauses;
    FULL
    LEFT
    RIGHT

    **Thus the clause OUTER JOIN when used without FULL, LEFT or RIGHT always results in an error

    **The clause NATURAL OUTER JOIN always results in an error

    ** These "things" are not commands, they are clauses. Clauses are also keywords.

    How am I doing? I am sure there are some mistakes. There is a lot more to JOIN clauses than first meets the eye.

    PS - I am using postgreSQL, the most awkwardly named application I have yet to come across.
    Last edited by enuenu; 08-28-08 at 02:34.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    NATURAL JOIN means join on the foreign keys, not attributes with common names.

    How an RDBMS handles exceptions depends on the RDBMS.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Aug 2008
    Location
    Australia
    Posts
    23
    So NATURAL JOIN does nothing unless primary and foreign keys have been explicitly defined? I did some experiments where I created two simple tables each with 2 attributes. Each table had one attribute in common (same name and type). I defined no keys explicitly.The NATURAL JOIN seemed to automatically recognize the common attributes and created the join on it.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by pootle flump
    NATURAL JOIN means join on the foreign keys, not attributes with common names.
    Not so: it does indeed mean join on attributes with common names!

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oops - it's been a while - I misremembered. Apols
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by andrewst
    Not so: it does indeed mean join on attributes with common names!
    which is why it's so dangerous, and not to be trusted in any sane person's database
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Aug 2008
    Location
    Australia
    Posts
    23
    So it seems it is preferable to use the ON operator/clause (what are these "things" called?) and explicitly define what attributes joins are to be based upon. Where do keys come in? Even when using ON I believe you need to specify the attributes exactly. Can the use of keys somehow negate the need to explicitly define the attributes within the boolean expression that follows ON?

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i call it the ON clause

    i don't understand your question "where do keys come in" because you are allowed to write joins to join on whatever columns you want

    p.s. you've apparently missed out on the USING clause in joins -- but, like NATURAL, you shouldn't be using USING either
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by r937
    i don't understand your question "where do keys come in" because you are allowed to write joins to join on whatever columns you want
    I think eneunu means, which JOIN type works by fuguring out approriate joins from the declared forieign key constraints. To which the answer is: none.

    Quote Originally Posted by r937
    p.s. you've apparently missed out on the USING clause in joins -- but, like NATURAL, you shouldn't be using USING either
    I'm totally with you on NATURAL being seriously dangerous - but why USING? You are explicitly naming the join columns, so there is no ambiguity. Not that I have used it more than a couple of times, ever.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by andrewst
    II'm totally with you on NATURAL being seriously dangerous - but why USING? You are explicitly naming the join columns, so there is no ambiguity. Not that I have used it more than a couple of times, ever.
    see http://www.dbforums.com/t997991.html for an example
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    OK, I'm convinced!

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Nice archiving, big cat
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •