Which join syntax is better?

Question

So, we are moving from Informix to Sql Server. And I noticed that in Informix, requests are written as follows:

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

While all the queries that 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: the first request is bad. This probably creates this huge set of records and then erases the actual set of records using the Where clause. Therefore, it is bad for performance. And this is non-annecy. So this is doubly bad.

However, after some search on Google, it seems that they are both theoretically almost the same. And they are both compatible with ANSI.

So my questions are:

• Do both requests perform the same? IE works just as fast and always gives the same answer.
• Are both really compatible with ANSI?
• Are there any outstanding reasons why I should push one style to another? Or should I just leave one alone?
  
  
  
  
  
  Note. These are just sample requests. I saw that some queries (of the first type) join up to 5 tables at a time.

Answer 1

Good, "better" is subjective. There is some kind of style. But I will directly address your issues.

• Both perform the same
• Both options are compatible with ANSI.

• The problem with the first example is that

  • it is very easy to inadvertently receive a cross-product (since it is easier to abandon the criteria for joining)

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

  • since the syntax of the outer join in the old style (* =) is outdated ( it has been documented for a long time to return incorrect results ), when you need to introduce new joins you need to mix the new style and old style combinations ... why promote inconsistency?

  • although this is not entirely credible with best practices, Microsoft explicitly recommends the INNER / OUTER JOIN syntax

  • using the last method:

    • you use consistent join syntax regardless of internal / external
    • tougher (not impossible) to accidentally get a cross product
    • extracting join criteria from filter criteria may facilitate debugging

I wrote a message that Kevin pointed to .