Comma seperated Join vs INNER JOIN

  • Ryan Naude

    Ten Centuries

    Points: 1369

    Hi guys, I read once that the inner join was better. This morning I tried to prove it and came very short, I could see no difference in client statistics or execution plan between the below queries. Has anybody got any thoughts on this

    Select

    * FROM

    Table1 A

    INNER JOIN

    Table2 B

    ON A.ID = B.ID

    VS

    SELECT

    * FROM

    Table1 A, Table2 B

    WHERE

    A.ID = B.ID

  • Alexander G.

    SSCrazy

    Points: 2705

    AFAIK there is no difference regarding the handling of the database engine.

    I prefer the inner join: Say you want to change the inner to an outer join. That ist easy whereas to change the where condition is more difficult.

  • NicHopper

    SSCrazy Eights

    Points: 8856

    The comma seperated join, is an ANSI 89 standard join, whilst the other is the newer ANSI 92 standard join.

    I dont know about the performance benefits of 92 over 89, however I beleive the 89 standard is being deprecated.

  • Ian Scarlett

    SSC-Insane

    Points: 23197

    Using JOIN is considered better for reasons of standardisation and readability, not performance. For a simple query like yours, you will see no difference in the query plans.

    Using JOIN is the ANSI standard way of doing it. The old style outer join using *= has been deprecated in SQL2005, and you can only use OUTER JOIN syntax.

    From the readability side of things, it makes it obvious which are the JOIN columns, as opposed to those that are genuine WHERE filters.

  • Quatrei.X

    SSCrazy

    Points: 2697

    Hi there,

    In addition, join is used in the ANSI Standard... well that's what I heard...

    I didn't even know you could use commas since in almost all books and inforation on the net uses join...

    but now that I know that we can use commas... I think I'll stick with join. If you would have a lot of joins it's easier FOR ME to trace which columns came from what table and you can also put the WHERE filtering of each table on their ON clause.

    _____________________________________________
    [font="Comic Sans MS"]Quatrei Quorizawa[/font]
    :):D:P;):w00t::cool::hehe:
    MABUHAY PHILIPPINES!

    "Press any key...
    Where the heck is the any key?
    hmmm... Let's see... there's ESC, CTRL, Page Up...
    but no any key"
    - Homer Simpson
  • jeffstubing

    Valued Member

    Points: 70

    A comma separated join (CROSS JOIN) is great when you want to end up with ALL COMBINATIONS of both tables, which isn't often.  It is great when you're in an environment where the DBA policies do not allow variables (Yes, they exist!).  Simply create a 1 row temp table or CTE to contain all variables.  Cross joining to a one row table will not increase your volume because you're multiplying x 1.  Other than that it's better to use JOIN, LEFT JOIN or OUTER JOIN because they will reduce your volume.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply