4

Possible Duplicate:
Difference between Inner Join & Full join

What is the difference between these two, especially within SQL Server 2008 -

select * from table1 t1, table2 t2 where t1.col1 = t2.col1

AND

select * from table1 t1 INNER JOIN table2 t2 ON t1.col1 = t2.col1
Community
  • 1
  • 1
cherry
  • 43
  • 1
  • 4
  • There is no difference to the optimizer - the first uses ANSI-89 join syntax, the second uses the much preferred ANSI-92 syntax. I personally purge ANSI-89 syntax whenever possible, and then out the culprit on various social media =) – OMG Ponies Aug 19 '12 at 21:01
  • The second won't work as your `where` is meant to be `on`? – Ben Aug 19 '12 at 21:03
  • @Ben: You're right, I fixed it – OMG Ponies Aug 19 '12 at 21:04
  • Cartesian join is sometimes used as a synonym for an outer join (Cartesian product) - might be appropriate to change the title but don't know if this is correct terminology for the old syntax – dfb Aug 19 '12 at 21:15
  • In a comment you said you got different results. Are you sure? – paparazzo Aug 19 '12 at 22:44
  • 3
    One is like speaking in Shakespearean English. The other is like speaking in modern colloquial English. Use the language of today! – Gordon Linoff Aug 19 '12 at 22:49
  • And you cannot use left joins using the implicit syntax inthe newer version of SQL server and if you try to combine them, the results may be incorrect. – HLGEM Aug 30 '12 at 21:07

2 Answers2

2

They are the same.

But consider what your syntax would look like if you wanted to do an INNER JOIN and then also OUTER JOIN to a different table.

It's more consistent to follow the INNER JOIN syntax so that if you need to modify your SQL later, it's easier to do. Plus, consistency allows others to have a better idea of your intent.

David Hoerster
  • 28,421
  • 8
  • 67
  • 102
  • Thx for the replies. The two queries gave me different outputs. The actual row output was the same, however the first query gave me the same row as result multiple times (the number was equal to the rows in the table) I was then told by someone to use the second version. I came from a PL/SQL bkgrd and new to SQL Server and didnt knew I could use it this way. – cherry Aug 19 '12 at 22:38
1

The first is the old way of writing an inner join, the second is the way it's written after the join command was added to SQL.

As long as both ways are accepted, there is no difference at all in the result. The execution plans for the two queries will be identical.

The old way of writing a join is being phased out, and may be disallowed in certain modes in later versions of SQL Server. It's not in SQL Server 2008.

Guffa
  • 687,336
  • 108
  • 737
  • 1,005