Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    May 2009
    Location
    Moore, OK
    Posts
    282
    Thanks
    11
    Thanked 41 Times in 41 Posts

    Understanding the different types of Joins

    Okay so I have been trying to understand when to use each different type of join (inner, outer, left, etc). I have always written my queries for joins as such:

    Code:
    SELECT a.field_1 FROM Table_A as a, Table_B as b WHERE a.a_id=b.a_id && b.field_2='some_input';
    I never use the join statement for say... mainly because I didn't understand it when I first began programming and I have got in a habit of not using them. I was curious as to why my method worked and if it had any drawbacks to doing it this way. So I read the manual for Joins on the MySQL website... which enlightened me some but still left me with questions, so I thought I'd ask you all.

    Basically what I got from reading it was theuse of the comma takes the place of the common join (or left join if I understood it properly). Then my ON statement was placed into the WHERE statement which is okay but not recommended just for organization sake (which I completely understand because I started placing all of these statements at the very beginning of my where statements to organize them). So my query should be reading...

    Code:
    SELECT a.field_1
    FROM Table_A as a, Table_B as b 
    ON (a.a_id=b.a_id)
    WHERE b.field_2='some_input';
    
    -- Or (if I am understanding right)
    SELECT a.field_1
    FROM Table_A as a 
    JOIN Table_B as b
    ON (a.a_id=b.a_id)
    WHERE b.field_2='some_input';
    
    -- Or my favorite two discoveries the USING or NATURAL JOINS
    SELECT a.field_1
    FROM Table_A as a 
    JOIN Table_B as b 
    USING (a_id)
    WHERE b.field_2='some_input';
    
    SELECT a.field_1
    FROM Table_A as a 
    NATURAL JOIN Table_B as b 
    WHERE b.field_2='some_input';
    So would there be any problems (syntax, portability, performance) to write the query as such:
    Code:
    SELECT a.field_1
    FROM Table_A as a, Table_B as b 
    USING (a_id)
    WHERE b.field_2='some_input';
    Also when would I know to use the different types of JOINS... everything I have ever written was accomplished using the ',' method and placing the ON statement into the WHERE clause as noted at the beginning. Any clarification would be appreciated. Thanks.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,635 Times in 4,597 Posts
    Actually, you have *NOT* describe any "different types of joins."

    All those are one and the same type of join: An INNER join. Although the syntax differs, the *SEMANTICS* are identical and will produce (nearly) identical results [I believe that only if you use SELECT * will you see differences in the fields returned when USING or NATURAL are used, and if you had multiple columns in common in the two tables, then NATURAL might change things].

    Now... I dunno if you noticed, but the *primary* MySQL documentation doesn't even MENTION either NATURAL or USING. You have to search through bug reports and enhancement lists, etc., to find the details. (That's true of 5.1 docs...I didn't check the version 6 docs.) Both appear to have arisen out of Oracle and have achieved ANSI blessing but not universal acceptance.

    And by that I mean (dunno if you care about this) that SQL Server, for a prime example, does not support *either* NATURAL or USING. (Again, that's true of SQL Server 2005, not sure about SQL Server 2008.)

    So...

    Personally, I would avoid NATURAL and USING, but your mileage may vary.

    This does leave us the two primary kinds of INNER JOINs that *are* supported by all major DBs and *are* part of ANSI SQL.
    Code:
    SELECT a.field_1
    FROM Table_A as a, Table_B as b 
    WHERE (a.a_id=b.a_id)    -- the parentheses are optional
    AND  b.field_2='some_input';
    
    SELECT a.field_1
    FROM Table_A as a 
    [INNER] JOIN Table_B as b
    ON (a.a_id=b.a_id)      -- the parentheses there are optional
    WHERE b.field_2='some_input';
    Once again, despite the difference in syntax, those two really and truly are 100% equivalent in meaning and will be executed by the query engine identically.

    The form using just a comma and the WHERE clause is the older form, predating ANSI SQL by many years. But it was (and is) in such common usage that the ANSI committee left it alone. My personal name for it is an "IMPLICIT inner join" where as the other form, that actually uses the JOIN keyword, is an "EXPLICIT inner join." [EDIT: Ha! Just saw that the Wikipedia article also uses implicit/explicit! Smart article. <grin/>]

    Yes, the keyword INNER is properly a part of the name of the JOIN, and *I* always use that keyword, for clarity. But if you omit it, then it is implied, anyway.

    So far as I can see, both of the other two forms--NATURAL and USING--are just "syntactic sugar" (look that up in Wikipedia...a "right on" definitions, though the Wikipedia article on SQL notes that they aren't *quite* just sugar). And I see no reason to use them when they aren't supported by all DBs. For the sake of saving the typing of a few characters, you end up with code that won't translate.

    NOW....

    I really *THOUGHT* your question (from the title) was going to ask about the *TRUE* different kinds of joins: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, and CROSS joins. For those, I commend you to this starting point:
    http://en.wikipedia.org/wiki/Join_(SQL)

  • Users who have thanked Old Pedant for this post:

    Coyote6 (10-26-2009)

  • #3
    Regular Coder
    Join Date
    May 2009
    Location
    Moore, OK
    Posts
    282
    Thanks
    11
    Thanked 41 Times in 41 Posts
    Thanks OP. I was asking about the "TRUE" different types too. I couldn't get my head around it. The wiki article explained it pretty well thanks... A few more questions.

    So are there any differences in a CROSS and an INNER join without an ON or WHERE statement?

    Code:
    SELECT *
    FROM   employee CROSS JOIN department
    
    -- is the same as
    SELECT *
    FROM   employee, department;
    
    -- and if I understand right, this is the same as the two above?
    SELECT *
    FROM employee JOIN department;
    If you use INNER, CROSS, and (LEFT) OUTER you can accomplish all of the tasks you need, right? (Since RIGHT can be done with a LEFT statement if you reverse the tables and FULL can be created with a UNION and LEFT statements.)

    I guess it was just the way you can accomplish so many of the same results with all of the different syntaxes that was making it difficult and to use OUTER and CROSS to retrieve NULL values.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,635 Times in 4,597 Posts
    Short answer regarding the cross join variants: No semantic difference.

    ***********

    I, personally, have found maybe 1 occasion in a thousand to use a CROSS join. THere just aren't many circumstances where you want all records from two tables matched up in every possible way.

    ************
    If you use INNER, CROSS, and (LEFT) OUTER you can accomplish all of the tasks you need, right?
    Well, I'd argue that CROSS is unnecessary, since you can accomplish it using INNER. And even if you run into a DB that doesn't allow an INNER join without an ON clause, you can always do
    Code:
    SELECT * FROM A INNER JOIN B ON 1=1
    And just as I find almost no use for a cross join, I also find almost no use for a FULL OUTER join. Anyway... While you *could* accomplish the same thing with UNION and a pair of LEFT joins, I'd say that would be a clumsy way to do it. Probably wouldn't perform as well as an explicitly specified FULL OUTER join.

    Finally, there *are* occasions where I find a RIGHT JOIN handy.

    For example, if I have a whole raft of tables that need to be INNER joined and only one table that needs to be outer joined, it's much simpler and more natural to code
    Code:
    SELECT ...
    FROM A 
    INNER JOIN B ON xxx
    INNER JOIN C ON xxx
    INNER JOIN D ON xxx
    RIGHT JOIN E ON xxx
    If you only had LEFT JOIN, then you'd have to use parentheses on the aggregation of the inner joins, and differenent DBs handle (or mangle! in the case of Access, for example) parenthesized table groupings better/worse/unexpectedly. (Actually, Access requires parens for any join of more than two tables...and they get really messy if you aren't careful.)

    So...

    I admit to taking the conservative approach: I try to write queries that are usable (or easily adaptable) to most any DB. But of course then the DBs go out of their way to make that hard to do with the utter lack of standardization of date/time data types and function and the plethora of incompatible string functions.

  • Users who have thanked Old Pedant for this post:

    Coyote6 (10-27-2009)

  • #5
    Regular Coder
    Join Date
    May 2009
    Location
    Moore, OK
    Posts
    282
    Thanks
    11
    Thanked 41 Times in 41 Posts
    Quote Originally Posted by Old Pedant View Post
    I admit to taking the conservative approach: I try to write queries that are usable (or easily adaptable) to most any DB. But of course then the DBs go out of their way to make that hard to do with the utter lack of standardization of date/time data types and function and the plethora of incompatible string functions.
    Yea that is the approach I was trying to move towards because of my work making switch to SQL from MySQL and I wanted my queries as portable as possible. I feel a lot better now that I understand the reasons to use the other types, I just haven't run into too much of a need for them (yet).


  •  

    Tags for this Thread

    Posting Permissions

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