交叉连接差异问题
可能的重复：
WHERE 子句最好在 IN 和 JOIN 之前或之后执行
INNER JOIN 与 WHERE 子句 - 有什么区别吗？
什么（如果有）以下之间有什么区别？
Select
col1,
col2
from TableA A, TableB B
where A.ID = B.ID
他们
Select
col1,
col2
From TableA A
Inner Join TableB B on A.ID = B.ID
在 SQL 中似乎有相同的行为，
Possible Duplicates:
WHERE clause better execute before IN and JOIN or after
INNER JOIN versus WHERE clause — any difference?
What, if any, are the differences between the following?
Select
col1,
col2
from TableA A, TableB B
where A.ID = B.ID
and
Select
col1,
col2
From TableA A
Inner Join TableB B on A.ID = B.ID
They seem to have the same behaviors in SQL,
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
它们可能会被 RDBMS 优化为相同的东西。他们都根据
A.ID = B.ID标准JOIN表。然而，
JOIN语法是明确的并且被认为是正确的。They will likely be optimized to the same thing by the RDBMS. They both
JOINthe tables on theA.ID = B.IDcriteria.However, the
JOINsyntax is explicit and considered correct.前者是ANSI-89语法，后者是ANSI-92语法。几乎应该始终使用后者，因为当您开始使用以 ANSI-92 语法表示的外连接时，它会更加清晰。
The former is ANSI-89 syntax, and the latter is ANSI-92 syntax. The latter should almost always be used due to the fact that it's much clearer when you start to use outer joins when expressed in ANSI-92 syntax.
第一个语法是（正如您所指出的）两个表的交叉联接或笛卡尔积。在没有优化器（或优化器较差）的系统中，这将生成第一个表中的每个记录与第二个表中的每个记录的组合，然后将它们过滤到仅匹配 WHERE 子句的记录。
两个语句的输出将是相同的，如果您使用的系统具有良好的优化器，那么性能也将是相同的。
我想提出两个意见：
1）我发现在撰写声明时最好明确表达您的意图。如果您打算执行 INNER JOIN，则使用 INNER JOIN 语法。未来6个月的你将会心存感激。
2）在这种情况下，SQL Server中的优化器将执行INNER JOIN（至少是最近的版本，不能保证所有版本），但是它猜测该路径的程度取决于SQL Server引擎的版本，并且是不保证将来保持不变（我怀疑在这种情况下它会改变，但是输入更多字符的成本真的那么高吗？）
The first syntax is (as you pointed out) a cross join or Cartesian product of the two tables. In a system with no optimizer (or a poor optimizer) this will produce a combination of every record in the first table combined with every record in the second table, then filter them down to just those matching the WHERE clause.
The output from both statements will be the same, and if the system you are using has a good optimizer than the performance will be the same as well.
Two comments I would offer:
1) I find it better to be explicit about your intent when writing statements. If you intended to perform an INNER JOIN then use the INNER JOIN syntax. Future you 6 months form now will be thankful.
2) The optimizer in SQL Server will perform an INNER JOIN in this situation (at least recent versions, can't guarantee all versions), but how well it guesses that path is going to depend on the version of the SQL Server engine and is not guaranteed to remain the same in the future (I doubt it will change in this situation, but is the cost of a few more characters of typing really that high?)
@ypercube 正确地指出你的问题是关于两种不同的 INNER JOIN 语法。您没有任何外连接语法。正如 @Matt Whitfield 指出的，第一个语法是 ANSI-92，第二个语法是 ANSI-89 样式。我完全同意 matt 的观点，即在更复杂的查询中，ANSI-92 语法更具可读性。
此外，根据您的 SQL Server 版本，ANSI-89 语法已被弃用，并且可能会给您带来问题。请参阅 SR0010：连接表时避免使用已弃用的语法或视图 事实上，在下一版本的 SQL 2011 或 Denali 或无论我们如何称呼它时，将不支持 ANSI-89 语法。请参阅：下一版本的 SQL Server 不支持的功能
（搜索单词“加入”）。
@ypercube correctly pointed out your question is about two different INNER JOIN syntaxes. You don't have any outer join syntax. As @Matt Whitfield pointed out, the first syntax is ANSI-92 and the second one is ANSI-89 style. I agree with matt entirely that in more complicated queries the ANSI-92 syntax is way way more readable.
Furthermore, depending on your version of SQL Server THE ANSI-89 syntax is DEPRECATED and can give you problems. See SR0010: Avoid using deprecated syntax when you join tables or views In fact, in the next version SQL 2011, or Denali, or whatever we're calling it, the ANSI-89 syntax will not be supported. See: Features Not Supported in the Next Version of SQL Server
(search for the word "join").