SQL JOIN 比 WHERE 快吗？

Is a JOIN faster than a WHERE?

提问by Wookai

Suppose I have two tables that are linked (one has a foreign key to the other) :

假设我有两个链接的表（一个有一个外键到另一个）：

CREATE TABLE Document (
  Id INT PRIMARY KEY,
  Name VARCHAR 255
)

CREATE TABLE DocumentStats (
  Id INT PRIMARY KEY,
  DocumentId INT, -- this is a foreign key to table Document
  NbViews INT
)

I know, this is not the smartest way of doing things, but this is the best example I could come up with.

我知道，这不是最聪明的做事方式，但这是我能想到的最好的例子。

Now, I want to get all documents that have more than 500 views. The two solutions that come to my mind are :

现在，我想获取浏览次数超过 500 次的所有文档。我想到的两个解决方案是：

SELECT *
FROM Document, DocumentStats
WHERE DocumentStats.Id = Document.Id
  AND DocumentStats.NbViews > 500

or :

或者 ：

SELECT *
FROM Document
INNER JOIN DocumentStats
ON Document.Id = DocumentStats.Id
WHERE DocumentStats.NbViews > 500

Are both queries equivalent, or is there one way that is far better than the other ? If so, why ?

两个查询是否等效，还是有一种方法比另一种方法好得多？如果是这样，为什么？

I'm aware that my example is not perfect, and that the queries may need some tune-up, but I hope you got the point ;) !

我知道我的示例并不完美，并且查询可能需要进行一些调整，但我希望您明白这一点；)！

EDIT: as requested in the answers, this question was aimed at MSSQL, but I would be interested in knowing if it is different for other DB engines (MySQL, etc...)

编辑：按照答案中的要求，这个问题针对的是 MSSQL，但我很想知道它是否与其他数据库引擎（MySQL 等）不同

采纳答案by Mehrdad Afshari

Theoretically, no, it shouldn't be any faster. The query optimizer should be able to generate an identical execution plan. However, some DB engines can produce better execution plans for one of them (not likely to happen for such a simple query but for complex enough ones). You should test both and see (on your DB engine).

理论上，不，它不应该更快。查询优化器应该能够生成相同的执行计划。然而，一些数据库引擎可以为其中之一生成更好的执行计划（对于这样一个简单的查询，但对于足够复杂的查询，不太可能发生）。您应该测试两者并查看（在您的数据库引擎上）。

回答by Mike J

Performance of "JOIN" versus "WHERE"... everything hinges on how well the database engine is able to optimize the query for you. It will take into account any indexes you might have on the columns being returned and consider that performance of WHERE and JOIN clauses also come down to the physical database file itself and its fragmentation level and even the storage technology you use to store the database files on.

“JOIN”与“WHERE”的性能……一切都取决于数据库引擎能够为您优化查询的能力。它将考虑您在返回的列上可能拥有的任何索引，并考虑 WHERE 和 JOIN 子句的性能也归结为物理数据库文件本身及其碎片级别，甚至是用于存储数据库文件的存储技术.

MSSql server executes queries in the following order (this should give you an idea of the functions of the WHERE and JOIN clauses)

MSSql 服务器按以下顺序执行查询（这应该让您了解 WHERE 和 JOIN 子句的功能）

Microsoft Sql Server query process order

Microsoft Sql Server 查询流程顺序

the following is taken from the excellent series of books about Microsoft SQL Server, Inside Microsoft SQL Server 2005: T-SQL Queryingwhich can be found here

以下内容摘自有关 Microsoft SQL Server 的优秀系列书籍Inside Microsoft SQL Server 2005：T-SQL Querying，可在此处找到

(Step 8) SELECT (Step 9) DISTINCT (Step 11)
(Step 1) FROM left_table
(Step 3) join_typeJOIN right_table
(Step 2) ON join_condition
(Step 4) WHERE where_condition
(Step 5) GROUP BY group_by_list
(Step 6) WITH [CUBE|ROLLUP]
(Step 7) HAVING having_clause
(Step 10) ORDER BY order_by_list

(步骤8) SELECT (步骤9) DISTINCT (步骤11)
(步骤1) FROM left_table
(步骤3) join_typeJOIN right_table
(步骤2) ON join_condition
(步骤4) WHERE where_condition
(步骤5) GROUP BY group_by_list
(步骤6) WITH [CUBE|ROLLUP]
（第 7 步） HAVING having_clause
（第 10 步）ORDER BY order_by_list

回答by Sam Saffron

There is no way to correctly answer this without limiting to a target database.

在不限制目标数据库的情况下，没有办法正确回答这个问题。

For MS-SQL both queries result in the same execution plans, but keep in mind:

对于 MS-SQL，两个查询都会产生相同的执行计划，但请记住：

SELECT *
FROM Document, DocumentStats
WHERE DocumentStats.Id = Document.Id
  AND DocumentStats.NbViews > 500

Is really risky since it is easy to forget the join condition in the WHERE clause and end up with a nasty cross join.

真的很危险，因为很容易忘记 WHERE 子句中的连接条件，并以令人讨厌的交叉连接结束。

回答by Greg

In MySQL at least, they will both be optimized to the same query.

至少在 MySQL 中，它们都将针对相同的查询进行优化。

回答by Sev

It is a "standard" to use the INNER JOIN syntax, although practically equivalent. The main reason it should be used is for clarity and mobility purposes as it is consistent with OUTER JOIN syntax.

尽管实际上等效，但使用 INNER JOIN 语法是一种“标准”。应该使用它的主要原因是为了清晰和移动性目的，因为它与 OUTER JOIN 语法一致。

回答by Theo

When you use Sqlite: The where-syntax is slightly faster because Sqlite first translates the join-syntax into the where-syntax before executing the query.

当您使用 Sqlite 时： where-syntax 稍微快一点，因为 Sqlite 在执行查询之前首先将 join-syntax 转换为 where-syntax。

回答by HLGEM

Explicit joins are easier to maintain as the intent of the query is much clearer. Also they are not subject to accidental cross joins so if you have a cross join in the query, the maintainer knows it was intended to be there.

显式连接更容易维护，因为查询的意图更加清晰。此外，它们不会受到意外交叉联接的影响，因此如果您在查询中有交叉联接，维护者就会知道它是打算在那里出现的。

If you ever need to use outer joins, you should know that the *= syntax is deprecated in SQL Server and will soon be removed. Also it currently doesn't function as expected all the time and may not give correct results and thus should NEVER be used. Mixing explicit outer joins and where clause joins (implicit joins) makes a query much harder for a maintainer to read and understand.

如果您需要使用外连接，您应该知道 *= 语法在 SQL Server 中已被弃用，并将很快被删除。此外，它目前无法一直按预期运行，并且可能无法给出正确的结果，因此永远不应使用。混合显式外连接和 where 子句连接（隐式连接）会使维护人员更难阅读和理解查询。

回答by Cowan

If you're talking specifically about SQL Server, then you should definitely be using the INNER JOIN syntax. Apart from being (personal opinion alert!) easier to read and more clear in intent, there is, as of SQL Server 2005, no equivalent syntax for outer joins. The *= and =* syntax is not supported by default in 2005 -- you need to enable a compatibility mode to support it. It will eventually be removed, possibly as soon as the next release (or possibly not!)

如果您专门讨论 SQL Server，那么您绝对应该使用 INNER JOIN 语法。除了（个人意见警报！）更容易阅读和更清晰的意图之外，从 SQL Server 2005 开始，外连接没有等效的语法。2005 年默认不支持 *= 和 =* 语法——您需要启用兼容模式来支持它。它最终会被删除，可能会在下一个版本发布（或者可能不会！）

This means:

这意味着：

• If you need to change a query from inner join to outer join, you need to either rewrite it (argh) or enable compat mode (yuk)
• Without compat mode, you can't be consistent with how you implement different types of joins (inner vs outer), making for a maintenance nightmare (and, where the two are combined in the one query, some behaviour that's non-intuitive).

• 如果需要将查询从内连接更改为外连接，则需要重写它 (argh) 或启用兼容模式 (yuk)
• 如果没有兼容模式，您将无法与实现不同类型的连接（内部与外部）的方式保持一致，从而导致维护噩梦（并且，在一个查询中将两者组合在一起时，某些行为是不直观的）。

Note also that contrary to popular belief, the two are notequivalent. Some things are much more awkward, and some are simply not possible. Kalen Delaney's Inside SQL Server 2000covers some examples; not sure if the newer editions do, because that join syntax is deprecated anyway.

另请注意，与流行的看法相反，两者并不等效。有些事情要尴尬得多，有些事情根本不可能。Kalen Delaney 的Inside SQL Server 2000涵盖了一些示例；不确定较新的版本是否可以，因为无论如何都不推荐使用该连接语法。

回答by samson

I guess that it doesn't make a difference too. To be sure you can check if the explain plan of those two queries is identical. In order to look at the explain plan in MySQL you have to put the "explain" keyword before the statement, eg:

我想这也没什么区别。为了确保您可以检查这两个查询的解释计划是否相同。为了查看 MySQL 中的解释计划，您必须在语句之前放置“explain”关键字，例如：

EXPLAIN
SELECT *
FROM Document, DocumentStats
WHERE DocumentStats.Id = Document.Id
  AND DocumentStats.NbViews > 500

I'm sure there exists an equivalent in MSSQL too.

我确定 MSSQL 中也存在等价物。

By the way: This looks like this is a 1:1 relationship so I'd just include the nbviews attribute directly in the Document table, therefore you can save a join.

顺便说一句：这看起来像是 1:1 的关系，所以我只是直接在 Document 表中包含 nbviews 属性，因此您可以保存连接。

回答by SqlRyan

In MSSQL, both queries are compiled to the same execution plan, so there's no difference. It's more about readability - I think the JOIN is easier to read, so I use that.

在 MSSQL 中，两个查询都编译为相同的执行计划，因此没有区别。更多的是关于可读性 - 我认为 JOIN 更容易阅读，所以我使用它。