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.

Revue

possible duplicate of Which one is better in SQL? – Andizhan

See a good post about it here: sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins – Skees

@Andizhan Well that question you linked to got closed as "not a real question". So are you saying that my question is "not a real question"? Because I would certainly disagree with that. – Revue

Old code in Informix is written using the old style 'comma list of table names in the FROM clause'. New code is written using the new (as in, since the 1992 SQL standard) notation with explicit JOIN operations. Use the new style. Period. End of discussion. Regard the old style as legacy only - to be upgraded ASAC (as soon as convenient). – Goodish

I've never had a problem using the old Informix notation. Have you thoroughly analyzed the pro's and con's of migrating to SQL Server? – Weatherford

@FrankComputer Well the migration isn't my call. I just got pulled into the aftermath. However, the Informix notation like the above works. I just didn't know if it would cause any unforeseen problems. Plus it was my first time dealing with SQL code like that. – Revue

Does this answer your question? Inner join vs Where – Royce

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.

Brittain

Also the old style left join can be misinterpreted by SQL Server even as far back as SQL Server 2000. See books online. – Schizo

Yeah I was not promoting the use of old-style outer joins at all. I should add that to that bullet. – Brittain

I know you were not. I have also noticed that there are issues tha crop up when combining explit and implicit syntax ( the execution plan may take you to a corss join them when it wouldn;t with all implicit joins), so at a minimum all left join queries should be written in explicit syntax. Personally I think having beeen replaced 20 years ago, it inexcusable that people still use implicit syntax. – Schizo

Oracle uses *= for outer joins; Informix uses a different syntax with the keyword OUTER in the FROM clause for the 'native (old style)' outer join. It uses the standard explicit JOIN (including OUTER JOIN) notation these days. – Goodish

@JonathanLeffler: I thought the *=/=* thing was SQL Server only. Did you mean Oracle used the (+) syntax for outer joins? – Dad

Aargh! You're right, @AndriyM; it was Sybase/SQL Server using *= and Oracle used (+) and Informix used OUTER (in a wholly different way from the SQL Standard), and ... before there was a standard, everyone did it their own different non-standard way. – Goodish

you cannot perform FULL OUTER JOIN using old syntax without using extra union query.. – Litho