哪个 SQL 语句更快？（拥有与在哪里......）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/328636/
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
Which SQL statement is faster? (HAVING vs. WHERE...)
提问by M_1
SELECT NR_DZIALU, COUNT (NR_DZIALU) AS LICZ_PRAC_DZIALU
FROM PRACOWNICY
GROUP BY NR_DZIALU
HAVING NR_DZIALU = 30
or
或者
SELECT NR_DZIALU, COUNT (NR_DZIALU) AS LICZ_PRAC_DZIALU
FROM PRACOWNICY
WHERE NR_DZIALU = 30
GROUP BY NR_DZIALU
回答by Vinko Vrsalovic
The theory (by theory I mean SQL Standard) says that WHERE restricts the result set before returning rows and HAVING restricts the result set after bringing all the rows. So WHERE is faster. On SQL Standard compliant DBMSs in this regard, only use HAVING where you cannot put the condition on a WHERE (like computed columns in some RDBMSs.)
理论（理论上我的意思是SQL Standard）说 WHERE 在返回行之前限制结果集，而 HAVING 在带来所有行之后限制结果集。所以 WHERE 更快。在这方面，在符合 SQL 标准的 DBMS 上，仅在不能将条件放在 WHERE 上的情况下使用 HAVING（如某些 RDBMS 中的计算列）。
You can just see the execution plan for both and check for yourself, nothing will beat that (measurement for your specific query in your specific environment with your data.)
您可以查看两者的执行计划并自行检查，没有什么比这更好的了（在您的特定环境中使用您的数据测量您的特定查询。）
回答by Eran Galperin
It might depend on the engine. MySQL for example, applies HAVING almost last in the chain, meaning there is almost no room for optimization. From the manual:
这可能取决于发动机。例如 MySQL，几乎在链的最后应用 HAVING，这意味着几乎没有优化空间。从手册：
The HAVING clause is applied nearly last, just before items are sent to the client, with no optimization. (LIMIT is applied after HAVING.)
HAVING 子句几乎最后应用，就在项目被发送到客户端之前，没有优化。（LIMIT 在 HAVING 之后应用。）
I believe this behavior is the same in most SQL database engines, but I can't guarantee it.
我相信这种行为在大多数 SQL 数据库引擎中都是一样的，但我不能保证。
回答by Mike Woodhouse
The two queries are equivalent and your DBMS query optimiser shouldrecognise this and produce produce the same query plan. It may not, but the situation is fairly simple to recognise, so I'd expect any modern system - even Sybase - to deal with it.
这两个查询是等效的，您的 DBMS 查询优化器应该认识到这一点并生成相同的查询计划。可能不是，但这种情况很容易识别，所以我希望任何现代系统——甚至是 Sybase——都能处理它。
HAVING clauses should be used to apply conditions on group functions, otherwise they can be mvoed into the WHERE condition. For example. if you wanted to restrict your query to groups that have COUNT(DZIALU) > 10, say, you would need to put the condition into a HAVING because it acts on the groups, not the individual rows.
HAVING 子句应该用于对组函数应用条件，否则它们可以被移动到 WHERE 条件中。例如。如果您想将查询限制为 COUNT(DZIALU) > 10 的组，例如，您需要将条件放入 HAVING 中，因为它作用于组，而不是单个行。
回答by programmer
Saying they would optimize is not really taking control and telling the computer what to do. I would agree that the use of having is not an alternative to a where clause. Having has a special usage of being applied to a group by where something like a sum() was used and you want to limit the result set to show only groups having a sum() > than 100 per se. Having works on groups, Where works on rows. They are apples and oranges. So really, they should not be compared as they are two very different animals.
说他们会优化并不是真正控制并告诉计算机要做什么。我同意使用 have 不能替代 where 子句。有一种特殊用法，即通过使用 sum() 之类的东西应用于组，并且您希望将结果集限制为仅显示 sum() 本身大于 100 的组。在组上工作，在行上工作。它们是苹果和橙子。所以真的，它们不应该被比较，因为它们是两种截然不同的动物。
回答by ysth
I'd expect the WHERE clause would be faster, but it's possible they'd optimize to exactly the same.
我希望 WHERE 子句会更快，但它们可能会优化为完全相同。
回答by Manoj Pandey
Both the statements will be having same performance as SQL Server is smart enough to parse both the same statements into a similar plan.
这两个语句将具有相同的性能，因为 SQL Server 足够聪明，可以将两个相同的语句解析为类似的计划。
So, it does not matter if you use WHERE or HAVING in your query.
因此，在查询中使用 WHERE 还是 HAVING 并不重要。
But, ideally you should use WHERE clause syntactically.
但是，理想情况下，您应该在语法上使用 WHERE 子句。
回答by Marius Gri
"WHERE" is faster than "HAVING"!
“WHERE”比“HAVING”更快！
The more complex grouping of the query is - the slower "HAVING" will perform to compare because: "HAVING" "filter" will deal with larger amount of results and its also being additional "filter" loop
查询的更复杂的分组是 - “HAVING”将执行比较慢，因为：“HAVING”“过滤器”将处理更多的结果，它也是额外的“过滤器”循环
"HAVING" will also use more memory (RAM)
“HAVING”也将使用更多内存（RAM）
Altho when working with small data - the difference is minor and can absolutely be ignored
尽管在处理小数据时 - 差异很小，绝对可以忽略
