Sunday, March 11, 2012

Compare join condition definited in from clause or where clause

such as:

1.select * from table1 join table2 on table1.column1=table2.column1

2.select * from table1,table2 where table1.column1=table2.column1

which one is better? why ?

thanks

As per the perfromance both or same.

The advantage on 1st qurey is ANSI standard, if you use/learn the ANSI it will be common across any databases.

|||

From: http://www.sql-server-performance.com/faq/sqlviewfaq.aspx?faqid=85

Which of the following joins will produce better performance?

ANSI JOIN Syntax


SELECT fname, lname, department
FROM names
INNER JOIN departments
ON names.employeeid = departments.employeeid

Former Microsoft JOIN Syntax


SELECT fname, lname, department
FROM names, departments
WHERE names.employeeid = departments.employeeid

Answer

SQL Server supports two variations of performing JOINs: the ANSI JOIN syntax and the former Microsoft JOIN syntax. Both produce identical results and identical performance. There is no performance reasons to use one form of the JOIN over the other.

On the other hand, there are two good reasons why you should use the ANSI JOIN syntax over the former Microsoft JOIN syntax. First, it is more portable because it is the ANSI standard, and second, because eventually Microsoft may eliminate support of the former JOIN syntax.

No comments:

Post a Comment