locked
Which query is better RRS feed

  • Question

  • select f.FileID from FilesVersions fv, Files2 f where fv.FileID = f.FileID and fv.FileVersionID = 0
    
    
    
    
    
    
    
    select f.FileID from FilesVersions fv join Files2 f on fv.FileID = f.FileID where fv.FileVersionID = 0
    
    
    
    



    Both returns same reqults, but I dunno which one is better and why ?


    X
    • Edited by xmen_xwk Monday, November 1, 2010 3:01 AM
    Monday, November 1, 2010 2:57 AM

Answers

  • The second variation with explicit JOINs is better. Using where style joins is an old syntax. ANSI standard dictates using JOIN syntax.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by xmen_xwk Monday, November 1, 2010 3:13 AM
    Monday, November 1, 2010 3:00 AM
  • You are correct.  They both will return the same result.  And they are both equally efficient.  And they are both ANSI standard compliant.  However, I and I think most SQL Server programmers, will recommend the second.  The first method is mostly a relic of the time with you could not do JOIN's in SQL Server.  The second method is a better style and is what is mostly used today.  In addition, if you are using outer joins, for example,

    select f.FileID from FilesVersions fv left outer join Files2 f on fv.FileID = f.FileID where fv.FileVersionID = 0

    and you cannot use the syntax where the join condition is placed in the WHERE clause.

    Tom

    • Marked as answer by xmen_xwk Monday, November 1, 2010 3:13 AM
    Monday, November 1, 2010 3:06 AM

All replies

  • The second variation with explicit JOINs is better. Using where style joins is an old syntax. ANSI standard dictates using JOIN syntax.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by xmen_xwk Monday, November 1, 2010 3:13 AM
    Monday, November 1, 2010 3:00 AM
  • ahh I thought using where style is new :D anyway thanks for the quick reply.


    X
    Monday, November 1, 2010 3:03 AM
  • You are correct.  They both will return the same result.  And they are both equally efficient.  And they are both ANSI standard compliant.  However, I and I think most SQL Server programmers, will recommend the second.  The first method is mostly a relic of the time with you could not do JOIN's in SQL Server.  The second method is a better style and is what is mostly used today.  In addition, if you are using outer joins, for example,

    select f.FileID from FilesVersions fv left outer join Files2 f on fv.FileID = f.FileID where fv.FileVersionID = 0

    and you cannot use the syntax where the join condition is placed in the WHERE clause.

    Tom

    • Marked as answer by xmen_xwk Monday, November 1, 2010 3:13 AM
    Monday, November 1, 2010 3:06 AM
  • and you cannot use the syntax where the join condition is placed in the WHERE clause.


    I dont get it
    X
    Monday, November 1, 2010 3:14 AM
  • and you cannot use the syntax where the join condition is placed in the WHERE clause.


    I dont get it
    X

    Before SQL Server allowed JOIN clauses, you used to do left and right outer joins by putting the join condition (fb.FileID = f.FileID in your case) in the WHERE clause with an *= to identify that it was a left outer join and an =* to identify a right outer join.  So

    select f.FileID from FilesVersions fv left outer join Files2 f on fv.FileID = f.FileID where fv.FileVersionID = 0

    would have been written

    select f.FileID from FilesVersions fv, Files2 f where fv.FileID *= f.FileID and fv.FileVersionID = 0

    but that syntax is no longer legal.

    Tom

    Monday, November 1, 2010 3:22 AM