Is there a difference using join andselect from multi-tables?

Question

First option:

SELECT Table1.* ,Table2.Price AS Price
FROM
  Table1,Table2
WHERE
  Table1.ID = Table2.ID 

Second option:

SELECT Table1.* ,Table2.Price AS Price
FROM 
  Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID

Which one is better and most efficient?

Answer 1

It may give you the same results, however the second option is better because it follows the latest standards, and properly defines what is the join and what may be a where clause. In terms of performance the two statements as above should perform the same.

Answer 2

The second way does the same thing, only with ANSI (American National Standards Institute standardized Structured Query Language) SQL, a standard form of SQL. Research it further to find its pros and cons.

http://it.toolbox.com/blogs/oracle-experience/a-case-for-ansi-sql-15647

Answer 3

They're both joins, the first is just using the implicit syntax and the second is using the explicit syntax.

Answer 4

Tons of duplicate questions

Also, it should be covered in any decent SQL book (I've got my understanding of the question from the O'Reilly pocket reference). Also, I still wonder where people get the old syntax examples, maybe I just learned SQL from the good sources. Also, while people repeat that both variants produce the same query with the same performance, I still prefer to doubt that when the talk is not about some DBMS specifically stating that in its documentation and especially when the talk is about DBMS in general.

Answer 5

the first query join style uses ancient join syntax, while the second style is more current. In most cases they result in the exact same query execution plan. However when using LEFT AND RIGHT OUTER JOINS, you can have probelms, see this answer and the comments:

ANSI joins versus "where clause" joins

from SQL Server BOL(2000) "In earlier versions of Microsoft® SQL Server™ 2000 left and right outer join conditions were specified in the WHERE clause using the *= and =* operators. In some cases, this syntax results in an ambiguous query that can be interpreted in more than one way.