JOIN比WHERE快吗? [英] Is a JOIN faster than a WHERE?

问题描述

假设我有两个链接的表(一个表到另一个表有外键):

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.

现在，我想获取所有具有500多个视图的文档.我想到的两个解决方案是:

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

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

或:

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 ;) !

按照答案的要求，这个问题针对的是MSSQL，但我想知道它是否与其他数据库引擎(MySQL等)不同?

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...)

推荐答案

从理论上讲，不，它不应该更快.查询优化器应该能够生成相同的执行计划.但是，某些数据库引擎可以为其中一个生成更好的执行计划(对于这样简单的查询，不太可能发生，但对于足够复杂的查询，则不会发生).您应该同时测试它们并查看(在您的数据库引擎上).

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).

这篇关于JOIN比WHERE快吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！