6

I get the same result in both queries below and same execution plan, any difference? or it is just how I prefer to write my query?

SELECT PS.StepID,PR.ProgramID FROM ProgramSteps PS, Programs PR
WHERE PS.ProgramID = PR.ProgramID

SELECT PS.StepID,PR.ProgramID FROM ProgramSteps PS
INNER JOIN Programs PR ON PS.ProgramID = PR.ProgramID
Victor Hugo Terceros
  • 2,969
  • 3
  • 18
  • 31
  • The duplicate marked will explain further, but as a general rule of thumb, you should *always* use `JOIN` instead of comma. – Tyler Roper Aug 31 '17 at 14:45
  • 1
    They generate the same plan, but the implicit (`,`) was deprecated **[over 25 years ago](http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt)**, and the explicit (`JOIN`) is the standard. – Siyual Aug 31 '17 at 14:46
  • Tip of today: Use the modern, explicit `JOIN` syntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed. – jarlh Aug 31 '17 at 14:46
  • Both your queries should yield identical result sets, but the comma version is deprecated as of the ANSI-92 SQL standard, which came out more than 25 years ago. As a rule of thumb, and as Gordon Linoff has stated hundreds of times on SO, _always_ use explicit joins. – Tim Biegeleisen Aug 31 '17 at 14:46
  • Promote the use of explict `JOIN` sintaxis, Aaron Bertrand wrote a nice article [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) about it. – Juan Carlos Oropeza Aug 31 '17 at 14:46
  • Yes, deprecated but I am using SQL Server 2016 and still available – Victor Hugo Terceros Aug 31 '17 at 14:47
  • 2
    @VictorHugoTerceros Yes, the implicit `INNER JOIN` *is* still available, but the `OUTER JOIN` implicit syntax (`*=`) is not supported in SQL Server 2016 at all. – Siyual Aug 31 '17 at 14:50
  • 2
    @Siyual - the (`,`) has **not** been deprecated. You posted the standard. I searched it. I would be grateful if you could point to the page(s) where it says that the comma is deprecated... – Vérace May 02 '21 at 09:06

1 Answers1

6

One difference is that the first option hides the intent by expressing the join condition in the where clause.

The second option, where the join condition is written out is more clear for the user reading the query. It shows the exact intent of the query.

As far as performance or any other difference, there shouldn't be any. Both queries should return the exact same result and perform the same under most RDBMS.

And as @Tim Biegeleisen says in the comments:

the comma version is deprecated as of the ANSI-92 SQL standard

myst1c
  • 593
  • 3
  • 13