WHERE和HAVING之间的语义差别是什么？ [英] What is the semantic difference between WHERE and HAVING?

问题描述

让我们把 GROUP BY 放在一边。在正常查询（不含 GROUP BY ）中，语义差异是什么？为什么此答案有效？ （在 HAVING 而不是 WHERE 中放置别名）

Let's put GROUP BY aside for a second. In normal queries (without GROUP BY), what is the semantic difference? Why does this answer work? (put an alias in a HAVING clause instead of WHERE)

推荐答案

HAVING 操作在摘要行 - WHERE 应用 GROUP BY 之前的整个表。 （您不能将 GROUP BY 放在一边， HAVING 是保留用于 GROUP BY - 省略 GROUP BY 不会更改幕后发生的隐式操作。）

HAVING operates on the summarized row - WHERE is operating on the entire table before the GROUP BY is applied. (You can't put GROUP BY aside, HAVING is a clause reserved for use with GROUP BY - leaving out the GROUP BY doesn't change the implicit action that is occurring behind the scenes).

还需要注意的是，因为 WHERE 可以使用索引，而 HAVING 不能。 （在非常简单的未分组结果集中，你理论上可以使用 HAVING 的索引，但我从来没有见过这样实现的查询优化器）。

It's also important to note that because of this, WHERE can use an index while HAVING cannot. (In super trivial un-grouped result sets you could theoretically use an index for HAVING, but I've never seen a query optimizer actually implemented in this way).

这篇关于WHERE和HAVING之间的语义差别是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！