显式与隐式 SQL 连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44917/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Explicit vs implicit SQL joins
提问by dmanxiii
Is there any efficiency difference in an explicit vs implicit inner join? For example:
显式和隐式内部连接是否存在效率差异？例如：
SELECT * FROM
table a INNER JOIN table b
ON a.id = b.id;
vs.
对比
SELECT a.*, b.*
FROM table a, table b
WHERE a.id = b.id;
采纳答案by lomaxx
Performance wise, they are exactly the same (at least in SQL Server).
性能方面，它们完全相同（至少在 SQL Server 中）。
PS: Be aware that the IMPLICIT OUTER JOINsyntax is deprecated since SQL Server 2005. (The IMPLICIT INNER JOINsyntax as used in the question is still supported)
PS：请注意，IMPLICIT OUTER JOIN自 SQL Server 2005 以来不推荐使用该IMPLICIT INNER JOIN语法。（仍然支持问题中使用的语法）
Deprecation of "Old Style" JOIN Syntax: Only A Partial Thing
回答by grom
Personally I prefer the join syntax as its makes it clearer that the tables are joined and how they are joined. Try compare larger SQL queries where you selecting from 8 different tables and you have lots of filtering in the where. By using join syntax you separate out the parts where the tables are joined, to the part where you are filtering the rows.
我个人更喜欢 join 语法，因为它更清楚地说明了表的连接方式以及它们的连接方式。尝试比较较大的 SQL 查询，您从 8 个不同的表中进行选择，并且在其中进行了大量过滤。通过使用连接语法，您可以将表连接的部分与过滤行的部分分开。
回答by Matt Fenwick
On MySQL 5.1.51, both queries have identical execution plans:
在 MySQL 5.1.51 上，两个查询具有相同的执行计划：
mysql> explain select * from table1 a inner join table2 b on a.pid = b.pid;
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------+
| 1 | SIMPLE | b | ALL | PRIMARY | NULL | NULL | NULL | 986 | |
| 1 | SIMPLE | a | ref | pid | pid | 4 | schema.b.pid | 70 | |
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------+
2 rows in set (0.02 sec)
mysql> explain select * from table1 a, table2 b where a.pid = b.pid;
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------+
| 1 | SIMPLE | b | ALL | PRIMARY | NULL | NULL | NULL | 986 | |
| 1 | SIMPLE | a | ref | pid | pid | 4 | schema.b.pid | 70 | |
+----+-------------+-------+------+---------------+------+---------+--------------+------+-------+
2 rows in set (0.00 sec)
table1has 166208 rows; table2has about 1000 rows.
table1有 166208 行；table2大约有 1000 行。
This is a very simple case; it doesn't by any means prove that the query optimizer wouldn't get confused and generate different plans in a more complicated case.
这是一个非常简单的案例；它无论如何都不能证明查询优化器不会在更复杂的情况下混淆并生成不同的计划。
回答by edosoft
The second syntax has the unwanted possibility of a cross join: you can add tables to the FROM part without corresponding WHERE clause. This is considered harmful.
第二种语法具有交叉连接的不希望的可能性：您可以在没有相应 WHERE 子句的情况下将表添加到 FROM 部分。这被认为是有害的。
回答by andy47
The first answer you gave uses what is known as ANSI join syntax, the other is valid and will work in any relational database.
您给出的第一个答案使用所谓的 ANSI 连接语法，另一个是有效的，可以在任何关系数据库中使用。
I agree with grom that you should use ANSI join syntax. As they said, the main reason is for clarity. Rather than having a where clause with lots of predicates, some of which join tables and others restricting the rows returned with the ANSI join syntax you are making it blindingly clear which conditions are being used to join your tables and which are being used to restrict the results.
我同意 grom 您应该使用 ANSI 连接语法。正如他们所说，主要原因是为了清楚。与其有一个包含大量谓词的 where 子句，其中一些连接表和其他限制使用 ANSI 连接语法返回的行，您正在盲目地清楚哪些条件用于连接您的表，哪些用于限制结果。
回答by Joshdan
Performance wise, they are exactly the same (at least in SQL Server) but be aware that they are deprecating this join syntax and it's not supported by sql server2005 out of the box.
性能方面，它们完全相同（至少在 SQL Server 中），但请注意，它们正在弃用此连接语法，并且 sql server2005 不支持开箱即用。
I think you are thinking of the deprecated *= and =* operators vs. "outer join".
我认为您正在考虑弃用的 *= 和 =* 运算符与“外连接”。
I have just now tested the two formats given, and they work properly on a SQL Server 2008 database. In my case they yielded identical execution plans, but I couldn't confidently say that this would always be true.
我刚刚测试了给定的两种格式，它们在 SQL Server 2008 数据库上正常工作。就我而言，它们产生了相同的执行计划，但我不能自信地说这总是正确的。
回答by deadbug
@lomaxx: Just to clarify, I'm pretty certain that both above syntax are supported by SQL Serv 2005. The syntax below is NOT supported however
@lomaxx：澄清一下，我很确定 SQL Serv 2005 支持以上两种语法。但是不支持以下语法
select a.*, b.*
from table a, table b
where a.id *= b.id;
Specifically, the outer join (*=) is not supported.
具体来说，不支持外连接 (*=)。
回答by Leigh Caldwell
On some databases (notably Oracle) the order of the joins can make a huge difference to query performance (if there are more than two tables). On one application, we had literally two orders of magnitude difference in some cases. Using the inner join syntax gives you control over this - if you use the right hints syntax.
在某些数据库（特别是 Oracle）上，连接的顺序会对查询性能产生巨大的影响（如果有两个以上的表）。在一个应用程序中，我们在某些情况下实际上有两个数量级的差异。如果使用正确的提示语法，则使用内部联接语法可让您控制这一点。
You didn't specify which database you're using, but probability suggests SQL Server or MySQL where there it makes no real difference.
您没有指定您使用的是哪个数据库，但概率表明 SQL Server 或 MySQL 在那里没有真正的区别。
回答by Mike McAllister
As Leigh Caldwell has stated, the query optimizer can produce different query plans based on what functionally looks like the same SQL statement. For further reading on this, have a look at the following two blog postings:-
正如 Leigh Caldwell 所说，查询优化器可以根据功能上看起来像相同 SQL 语句的内容生成不同的查询计划。如需进一步阅读，请查看以下两篇博文：-
One posting from the Oracle Optimizer Team
Another posting from the "Structured Data" blog
I hope you find this interesting.
我希望你觉得这很有趣。
回答by David
Performance wise, it should not make any difference. The explicit join syntax seems cleaner to me as it clearly defines relationships between tables in the from clause and does not clutter up the where clause.
性能明智，它不应该有任何区别。显式连接语法对我来说似乎更清晰，因为它清楚地定义了 from 子句中表之间的关系，并且不会弄乱 where 子句。
