I have some queries that look like this:
select a.ID, a.Customer, b.Sales
from a, b
where a.ID = b.ID
Yet I see many people writing code like this:
select a.ID, a.Customer, b.Sales
from a
inner join b
where a.ID = b.ID
Is there a difference between these? Does one perform better than the other?
Although there is no performance difference, the 2nd example (using the JOIN syntax in the FROM clause instead of using the WHERE clause) is the preferred method as recommended in the latest ISO standard. In fact, specifying the join in the WHERE clause is only ISO-compliant for an inner join.
The two queries has the same execution plan, there is no diffrerence between them. They are just two different ways for saying the same thing, that is to join the two tables on the given columns. So there are no performance differences either.
PS: You could misspelled the second codesnippet. If I'm right it should look like this:
select a.ID, a.Customer, b.Sales
from a
inner join b on a.ID = b.ID
In terms of performance and execution plans, there is no difference between these two, very simple queries. What you're seeing are ANSI 89 joins, where the tables are listed and the join criteria is in the where clause, versus ANSI 92 joins, where the join criteria listed in the ON statement.
For SQL Server, using INNER joins, there is effectively no difference. But, if you get into OUTER joins, you need to know that with SQL Server 2008, the syntax required for the older ANSI 89 standard is no longer supported. Also, it's worth noting, as your queries get more and more complex, you may begin to see differences in the execution plans generated between queries that use ANSI 89 and ANSI 92. Not because there is a difference, there isn't. But you're added overhead to the optimizer engine for it to attempt to find the join criteria in the WHERE clause. That added overhead could lead to the optimizer running out of time sooner on those queries, resulting in a less optimal plan. Also, since the majority of people working with SQL Server, as well most of the documentation, books, blogs, articles, use ANSI 92, in terms of code maintenance and your own understanding, you'd be better off following the ANSI 92 methodology of joins.
