Results 1 to 10 of 10

Thread: Join Opinion

  1. #1
    Join Date
    Sep 2003
    Posts
    364

    Unanswered: Join Opinion

    What is the better practice when joining tables in a query example 1 or 2? I've always joined tables together like example 2, but it seems that many people are advocates of example 1. Are there any differences performance wise between the two?

    Example 1:
    SELECT *
    FROM authors AS a INNER JOIN publishers AS p
    ON a.city = p.city
    ORDER BY a.au_lname DESC

    Example 2:
    SELECT *
    FROM authors AS a, publishers AS p
    WHERE a.city = p.city
    ORDER BY a.au_lname DESC

  2. #2
    Join Date
    Nov 2003
    Posts
    94
    Both examples are executionally identical.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I find the first way helps against inadvertant cross joins. Also, it is a lot easier to do the outer joins in the first method.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Both examples are executionally identical because the optimizer converts the second syntax to the first syntax, at least for simple queries such as your example. For more complex queries it is possible that the optimer will not be able to do this, and then the first syntax is preferable.

    blindman

  5. #5
    Join Date
    Dec 2003
    Posts
    454

    INNER JOIN

    For both queries you posted, I do not think there is a different performance between them, but INNER JOIN is a really good way to be used in a query statement. It also can be used in the WHERE clause.
    Last edited by gyuan; 01-07-04 at 14:46.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...ANSI...A NSI...ANSI...ANSI...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Brett, there's no need to get all ansi about it!

  8. #8
    Join Date
    Sep 2003
    Posts
    364
    Brett, tell me how you really feel about it LOL

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well,

    You could probably still use...

    Code:
    SELECT * 
      FROM Orders o, [Order Details] d 
     WHERE o.Orderid =* d.Orderid
    Then there Oracle

    Code:
    SELECT * 
      FROM Orders o, [Order Details] (+) d 
     WHERE o.Orderid = d.Orderid
    Or Informix

    Code:
    SELECT * 
      FROM Orders o, [Order Details]  d 
     WHERE o.Orderid = d.Orderid(+)

    Ya gotta love standards...

    But as far as performance goes...howzabout for nonexistance

    SELECT * FROM Orders o LEFT JOIN [Order Details] d
    WHERE o.OrderId = d.OrderId AND d.OrderId IS NULL

    instead on NOT EXISTS or NOT IN....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jul 2002
    Posts
    58

    You will never regret

    You will never regret using ANSI syntax when you go back to do maintenance.

    Separating the JOIN conditions from the WHERE conditions will always make things much clearer six months down the road when you are asking yourself 'Now what the hell is this query/view doing??????'

    The ANSI syntax may be more verbose, but is always clearer to understand when you come back to it later.

    Trust me, it grows on you. The more complex the queries you write, the more sense it makes. When you are writing a query like the example above....

    Code:
    SELECT *
    FROM authors AS a, publishers AS p
    WHERE a.city = p.city
    ORDER BY a.au_lname DESC
    ...it doesn't make much difference, but when you write something like this piece of a view definition.....

    Code:
    SELECT (huge select section left out for clarity)
       FROM table_case AS cs
       CROSS JOIN table_report_window AS rw
       JOIN table_gbst_elm AS elm ON cs.casests2gbst_elm = elm.objid
       JOIN table_close_case AS cc ON cc.last_close2case = cs.objid
       LEFT OUTER JOIN table_ibm_machine_type AS mt ON cs.case2machine_type = mt.objid
       LEFT OUTER JOIN table_atlas_dispatch AS ad ON cs.case2dispatch = ad.objid
       LEFT OUTER JOIN table_user AS usr ON cs.case_owner2user = usr.objid
       LEFT OUTER JOIN table_time_zone AS tmzn ON cs.case2time_zone = tmzn.objid
       LEFT OUTER JOIN table_contract AS cont ON cs.entitlement2contract = cont.objid
       LEFT OUTER JOIN table_act_entry AS ae200 ON ae200.act_entry2case = cs.objid
                                                                     AND ae200.entry_time = (SELECT MAX(aecsq1.entry_time)
                                                                                                FROM table_act_entry AS aecsq1
                                                                                                WHERE aecsq1.act_code = 200
                                                                                                      AND aecsq1.act_entry2case = cs.objid)
                                                                     AND ae200.act_code = 200
       LEFT OUTER JOIN table_act_entry AS ae92002 ON ae92002.act_entry2case = cs.objid
                                                                       AND ae92002.entry_time = (SELECT MIN(aecsq2.entry_time)
                                                                                                    FROM table_act_entry AS aecsq2
                                                                                                    WHERE aecsq2.act_code = 92002
                                                                                                          AND aecsq2.act_entry2case = cs.objid)
                                                                       AND ae92002.act_code = 92002
       LEFT OUTER JOIN table_atlas_part_order AS po1 ON po1.part_order2dispatch = cs.case2dispatch
                                             AND po1.objid = (SELECT MIN(objid)
                                                                 FROM table_atlas_part_order AS pocsq1
                                                                 WHERE pocsq1.part_order2dispatch = cs.case2dispatch)
       LEFT OUTER JOIN table_atlas_part_order AS po2 ON po2.part_order2dispatch = cs.case2dispatch
                                                        AND po2.objid = (SELECT MIN(objid)
                                                                            FROM table_atlas_part_order AS pocsq2
                                                                            WHERE pocsq2.part_order2dispatch = cs.case2dispatch
                                                                                  AND pocsq2.objid > (SELECT MIN(objid)
                                                                                                         FROM table_atlas_part_order AS pocsq3
                                                                                                         WHERE pocsq3.part_order2dispatch = cs.case2dispatch))
       LEFT OUTER JOIN table_demand_hdr AS hdr on hdr.caseinfo2case = cs.objid
       WHERE cc.close_date BETWEEN rw.start_date AND rw.end_date 
    UNION
    SELECT (huge select section left out for clarity)
       FROM table_demand_hdr AS hdr
       CROSS JOIN table_report_window AS rw
       JOIN table_case AS cs ON hdr.caseinfo2case = cs.objid
       JOIN table_gbst_elm AS elm1 ON cs.calltype2gbst_elm = elm1.objid
       JOIN table_gbst_elm AS elm2 ON cs.casests2gbst_elm = elm2.objid
       LEFT OUTER JOIN table_ibm_machine_type AS mt  ON cs.case2machine_type = mt.objid
       LEFT OUTER JOIN table_atlas_dispatch AS ad ON cs.case2dispatch = ad.objid
       LEFT OUTER JOIN table_user AS usr ON cs.case_owner2user = usr.objid
       LEFT OUTER JOIN table_time_zone AS tmzn ON cs.case2time_zone = tmzn.objid
       LEFT OUTER JOIN table_contract AS cont ON cs.entitlement2contract = cont.objid
       LEFT OUTER JOIN table_act_entry AS ae200 ON ae200.act_entry2case = cs.objid
                                                                     AND ae200.entry_time = (SELECT MAX(aecsq1.entry_time)
                                                                                                FROM table_act_entry AS aecsq1
                                                                                                WHERE aecsq1.act_code = 200
                                                                                                      AND aecsq1.act_entry2case = cs.objid)
                                                                     AND ae200.act_code = 200
       LEFT OUTER JOIN table_act_entry AS ae92002 ON ae92002.act_entry2case = cs.objid
                                                                       AND ae92002.entry_time = (SELECT MIN(aecsq2.entry_time)
                                                                                                    FROM table_act_entry AS aecsq2
                                                                                                    WHERE aecsq2.act_code = 92002
                                                                                                          AND aecsq2.act_entry2case = cs.objid)
                                                                       AND ae92002.act_code = 92002
       LEFT OUTER JOIN table_atlas_part_order AS po1 ON po1.part_order2dispatch = cs.case2dispatch
                                             AND po1.objid = (SELECT MIN(objid)
                                                                 FROM table_atlas_part_order AS pocsq1
                                                                 WHERE pocsq1.part_order2dispatch = cs.case2dispatch)
       LEFT OUTER JOIN table_atlas_part_order AS po2 ON po2.part_order2dispatch = cs.case2dispatch
                                                        AND po2.objid = (SELECT MIN(objid)
                                                                            FROM table_atlas_part_order AS pocsq2
                                                                            WHERE pocsq2.part_order2dispatch = cs.case2dispatch
                                                                                  AND pocsq2.objid > (SELECT MIN(objid)
                                                                                                         FROM table_atlas_part_order AS pocsq3
                                                                                                         WHERE pocsq3.part_order2dispatch = cs.case2dispatch))
       WHERE elm2.title = 'Cancel'
             AND hdr.x_req_type = 'Vendor Exchange'
       		AND elm1.title = 'Customer'
             AND cs.modify_stmp BETWEEN rw.start_date AND rw.end_date
    ... with multiple and cascaded correlated subqueries on outer joined tables, and multiple conditions on a single join, you will be VERY happy you used the ANSI syntax when it's time to make changes.

    Once you start joining several tables, it becomes difficult to tell where the join conditions end and the conditions that actually select the rows begin - and that's assuming you didn't intermix them. In the big unioin above, it's easy to see the two different selection criteria that are applied to what are in fact two very similar joins (they only differ by one table), but you don't need a union to make this separation of join conditions and where conditions handy. Get in the habit now.
    Last edited by Steve Duncan; 01-07-04 at 16:14.

Posting Permissions

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