locked
Which one is Better? RRS feed

  • Question

  • Create table T1

    (

    ID int,

    Name varchar(20)

    )

     

    Create table T2

    (

    ID int,

    City varchar(20)

    )

     

     

    Select T1.ID,T1.Name,T1.City

    from T1,T2

    where T1.ID = T2.ID

     

    Or

     

    Select T1.ID,T1.Name,T1.City

    from T1 Inner join T2  ON  T1.ID = T2.ID

     

    Based on performance which on will be better?

    Monday, December 8, 2008 12:39 PM

Answers

  •  Selvakumar_RSK wrote:

    Select T1.ID,T1.Name,T2.City

    from T1 Inner join T2  ON  T1.ID = T2.ID

    The above is the new JOIN syntax style.

    I suggest that you use it in the future.

    There is no difference in performance.

    Related INNER JOIN example:

    http://www.sqlusa.com/bestpractices2005/innerjoin/

    • Marked as answer by Mangal Pardeshi Saturday, December 13, 2008 6:08 PM
    • Edited by Kalman Toth Saturday, January 28, 2012 11:06 PM
    Monday, December 8, 2008 12:55 PM
  •  

    There is no difference. SQL Server will handle them in the same way and generate the same execution plan.
    Monday, December 8, 2008 12:56 PM
  • Hi Selva

     

    Both are same . they will have same execution Plan and query cost.

     

     

    When you say

    Code Snippet

    Select T1.ID,T1.Name,T1.City

    from T1,T2

     

     

    this is CROSS JOIN

     

    but when you say

    Code Snippet

    Select T1.ID,T1.Name,T1.City

    from T1,T2

    where T1.ID = T2.ID

     

     it works same as INNER JOIN . ( it is just the old way of doing the Inner join)

     

    Thus performance wise no difference. Readability wise the second one is better as you know.

    Monday, December 8, 2008 12:56 PM
  • Performance wise there is no difference.  Have a look at Database journal's Article

     

    http://www.databasejournal.com/features/mssql/article.php/1438001/ANSI-Joins.htm

    Monday, December 8, 2008 1:18 PM

All replies

  •  Selvakumar_RSK wrote:

    Select T1.ID,T1.Name,T2.City

    from T1 Inner join T2  ON  T1.ID = T2.ID

    The above is the new JOIN syntax style.

    I suggest that you use it in the future.

    There is no difference in performance.

    Related INNER JOIN example:

    http://www.sqlusa.com/bestpractices2005/innerjoin/

    • Marked as answer by Mangal Pardeshi Saturday, December 13, 2008 6:08 PM
    • Edited by Kalman Toth Saturday, January 28, 2012 11:06 PM
    Monday, December 8, 2008 12:55 PM
  •  

    There is no difference. SQL Server will handle them in the same way and generate the same execution plan.
    Monday, December 8, 2008 12:56 PM
  • Hi Selva

     

    Both are same . they will have same execution Plan and query cost.

     

     

    When you say

    Code Snippet

    Select T1.ID,T1.Name,T1.City

    from T1,T2

     

     

    this is CROSS JOIN

     

    but when you say

    Code Snippet

    Select T1.ID,T1.Name,T1.City

    from T1,T2

    where T1.ID = T2.ID

     

     it works same as INNER JOIN . ( it is just the old way of doing the Inner join)

     

    Thus performance wise no difference. Readability wise the second one is better as you know.

    Monday, December 8, 2008 12:56 PM
  • Performance wise there is no difference.  Have a look at Database journal's Article

     

    http://www.databasejournal.com/features/mssql/article.php/1438001/ANSI-Joins.htm

    Monday, December 8, 2008 1:18 PM