sql server - Which join syntax is better?

Question

So we are migrating from Informix to Sql Server. And I have noticed that in Informix the queries are written in this manner:

select [col1],[col2],[col3],[col4],[col5]
from tableA, tableB
where tableA.[col1] = table.[gustavs_custom_chrome_id]

Whereas all the queries I write in SQL Server are written as:

select [col1],[col2],[col3],[col4],[col5]
from tableA 
inner join tableB on tableA.[col1] = table.[gustavs_custom_chrome_id]

Now, my first thought was: that first query is bad. It probably creates this huge record set then whittles to the actual record set using the Where clause. Therefore, it's bad for performance. And it's non-ansi. So it's double bad.

However, after some googling, it seems that they both are, in theory, pretty much the same. And they both are ANSI compliant.

So my questions are:

1. Do both queries perform the same? IE. runs just as fast and always gives the same answer.
2. Are both really ANSI-compliant?
3. Are there any outstanding reasons why I should push for one style over another? Or should I just leave good enough alone?
   
   
   Note: These are just examples of the queries. I've seen some queries (of the first kind) join up to 5 tables at a time.

Answer 1

Well, "better" is subjective. There is some style here. But I'll address your questions directly.

1. Both perform the same
2. Both are ANSI-compliant.

3. The problem with the first example is that

   • it is very easy to inadvertently derive the cross product (since it is easier to leave out join criteria)

   • it also becomes difficult to debug the join criteria as you add more and more tables to the join

   • since the old-style outer join (*=) syntax has been deprecated (it has long been documented to return incorrect results), when you need to introduce outer joins, you need to mix new style and old style joins ... why promote inconsistency?

   • while it's not exactly the authority on best practices, Microsoft recommends explicit INNER/OUTER JOIN syntax

   • with the latter method:

     • you are using consistent join syntax regardless of inner / outer
     • it is tougher (not impossible) to accidentally derive the cross product
     • isolating the join criteria from the filter criteria can make debugging easier

I wrote the post Kevin pointed to.