Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2010
    Posts
    2

    Unanswered: Query optimization

    Would like to give a feedback on which would be a better way to write a query?

    Method 1
    select * from table1, table2 where table1.field1 = table2.field1 and table1.field2 = '5'

    Method 2
    select * from table1 left join table2 on table1.field1 = table2.field1 where table1.field2 = '5'

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by basky View Post
    Would like to give a feedback on which would be a better way to write a query?
    better? they incomparable, because those queries produce fundamentally different results

    the first query uses the deprecated "comma list" style of join, whereas the second query uses explicit JOIN syntax

    but it's apples to oranges

    the first query is an inner join, whereas the second query is a left outer join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2010
    Posts
    2
    Sorry the Method 2 query was wrong.
    Actually wanted to ask whether using "comma list" style join and using "inner join", will there be any performance difference?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by basky View Post
    Actually wanted to ask whether using "comma list" style join and using "inner join", will there be any performance difference?
    no, not in performance
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Assuming there is no performance difference, which is my general understanding, I prefer the JOIN syntax; it makes it easier for me to organize and display join criteria.

    For instance, I frequently see something like this (actually, this is cleaned-up compared to what I usually see):

    Code:
    select  t1.Field10,
            t2.Field11,
            t3.Field12
    from    dbo.tblTable1 t1,
            dbo.tblTable2 t2,
            dbo.tblTable3 t3
    where   t1.Field1=t2.Field1
            and t1.Field2=t2.Field2
            and t1.Field3=t2.Field3
            and t2.Field1=t3.Field1
            and t2.Field2=t3.Field2
            and t2.Field3=t3.Field3
    Here’s the same query using the JOIN style:

    Code:
    select  t1.Field10,
            t2.Field11,
            t3.Field12
    from    dbo.tblTable1 t1
    inner
    join    dbo.tblTable2 t2 on
                t2.Field1=t1.Field1
                and t2.Field2=t1.Field2
                and t2.Field3=t1.Field3
    inner
    join    dbo.tblTable3 t3 on
                t3.Field1=t2.Field1
                and t3.Field2=t2.Field2
                and t3.Field3=t2.Field3
    Notice how the join criteria is separated and associated with the table on-which the join is based. With the comma style, you have to read through every one of the where criteria to make sure you are not missing any join criteria. And, many times, I run across queries using the comma style, where all of the join criteria in the where clause are jumbled-up.

    Even though it does not make a difference, it is best to, early-on, get into the habit of using a fundamentally sound coding style. Even in the examples I have shown above, in the select clause, it is better to have the commas separating the fields occur at the beginning of the new line rather then the end of the old line. But I got into a bad habit of doing it as above, early-on, and it is really difficult to change my style, even though I know the other style is better.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by PracticalProgram View Post
    Even in the examples I have shown above, in the select clause, it is better to have the commas separating the fields occur at the beginning of the new line rather then the end of the old line.
    thank you so much for bringing this up

    it is sad indeed that so few people recognize the benefits of the "leading comma" style

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Quote Originally Posted by r937 View Post
    it is sad indeed that so few people recognize the benefits of the "leading comma" style
    It's sad to see how few people have any style at all !!! (second-meaning not intended, but true none-the-less).
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Posting Permissions

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