This question already has an answer here:
这个问题已经有了答案:
- INNER JOIN ON vs WHERE clause 10 answers
- 内连接在vs上第10条的答案。
I have two select join SQL statements:
我有两个select join SQL语句:
select a.id from table_a as a, table_b as b where a.id=b.id;
select a.id from table_a as a inner join table_b as b on a.id=b.id;
Obviously, they are the same in result. But is there any difference between them , such as performance, portability.
显然，结果是一样的。但是它们之间有什么区别吗，比如性能、可移植性。
4 个解决方案
#1
18
One difference is that the first option hides the intent by expressing the join condition in the where clause.
一个区别是，第一个选项通过在where子句中表示连接条件来隐藏意图。
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.
至于性能或其他差异，不应该有任何差异。两个查询应该返回完全相同的结果，并在大多数RDBMS下执行相同的操作。
#2
3
The inner join syntax was added to SQL sometime in the 1990s. It's possible, but unlikely, that the optimizer can do better with it than with the old syntax that used the where clause for the join condition.
内部连接语法在20世纪90年代的某个时候被添加到SQL中。与使用连接条件的where子句的旧语法相比，优化器可以更好地使用它，这是可能的，但不太可能的。
They should both be highly portable as things are now.
它们都应该像现在这样高度便携。
The inner join syntax is preferable because it is easier on the reader, as others have already remarked.
内部连接语法更可取，因为它对读者更容易，正如其他人已经注意到的那样。
#3
0
They are exactly the same in SQL server. There is no performance difference.
它们在SQL server中是完全相同的。没有性能差异。
#4
0
Both are standard SQL. Different DB systems may optimize them differently, but because they are so simple, I would be a little surprised if they do. But that is the nature of SQL: it is declarative, which gives the implementation a great deal of leeway in how to execute your query. There is no guarantee that these perform the same, or if they are different, which is faster.
都是标准的SQL。不同的DB系统可能会对它们进行不同的优化，但由于它们非常简单，如果它们这样做的话，我会有点惊讶。但这就是SQL的本质:它是声明性的，这给实现提供了很大的执行查询的余地。并不能保证它们的性能是相同的，或者如果它们是不同的，那么速度会更快。
#1
18
One difference is that the first option hides the intent by expressing the join condition in the where clause.
一个区别是，第一个选项通过在where子句中表示连接条件来隐藏意图。
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.
至于性能或其他差异，不应该有任何差异。两个查询应该返回完全相同的结果，并在大多数RDBMS下执行相同的操作。
#2
3
The inner join syntax was added to SQL sometime in the 1990s. It's possible, but unlikely, that the optimizer can do better with it than with the old syntax that used the where clause for the join condition.
内部连接语法在20世纪90年代的某个时候被添加到SQL中。与使用连接条件的where子句的旧语法相比，优化器可以更好地使用它，这是可能的，但不太可能的。
They should both be highly portable as things are now.
它们都应该像现在这样高度便携。
The inner join syntax is preferable because it is easier on the reader, as others have already remarked.
内部连接语法更可取，因为它对读者更容易，正如其他人已经注意到的那样。
#3
0
They are exactly the same in SQL server. There is no performance difference.
它们在SQL server中是完全相同的。没有性能差异。
#4
0
Both are standard SQL. Different DB systems may optimize them differently, but because they are so simple, I would be a little surprised if they do. But that is the nature of SQL: it is declarative, which gives the implementation a great deal of leeway in how to execute your query. There is no guarantee that these perform the same, or if they are different, which is faster.
都是标准的SQL。不同的DB系统可能会对它们进行不同的优化，但由于它们非常简单，如果它们这样做的话，我会有点惊讶。但这就是SQL的本质:它是声明性的，这给实现提供了很大的执行查询的余地。并不能保证它们的性能是相同的，或者如果它们是不同的，那么速度会更快。