question

Steve Jones - Editor avatar image
Steve Jones - Editor asked

Different query coding styles

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?

t-sqlbest-practice
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I think on the second one you meant "on" instead of where.
0 Likes 0 ·
I used my freedom to add the "best-practice" tag to your question, hope you don't mind.
0 Likes 0 ·
Tom Staab avatar image
Tom Staab answered

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.

4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Agreed. It is purely a stylistic thing, but that style can make a great deal of difference when it comes time for someone else to understand or update that code.
1 Like 1 ·
The thing I like about the JOIN syntax is that the condition is next to the tables concerned and not buried at the bottom in the WHERE clause. It's more clearly an issue when there are a lot of tables being joined.
1 Like 1 ·
I agree with you and I would say that join syntax is much easier to read, especially when you have a lot of joins and maybe even multiple columns for each join.
0 Likes 0 ·
Absolutely. I always recommend (insist on) using the join syntax.
0 Likes 0 ·
dillinzser avatar image
dillinzser answered

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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

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.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yup the OUTER JOIN is the gotcha here. Got to look out for that one.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.