为什么有“有"时为什么有“哪里" [英] Why do you have 'where' when there is 'having'

问题描述

我知道已经讨论了很多，但是我的研究都无法说服我MySQL中'where'和'having'子句之间的区别.据我了解，我们可以使用'having'实现'where'子句可以完成的所有工作.例如. select * from users having username='admin'.那为什么需要'where'子句?在何处使用会产生性能差异吗?

I know this is much discussed, but none of my research could convince me the difference between 'where' and 'having' clauses in MySQL. From what I understand we can achieve everything that can be done with 'where' clause using 'having' . For eg. select * from users having username='admin'. Then why do you need 'where' clause? Does using where make any performance differences?

推荐答案

WHERE子句在聚合之前从源中过滤数据，而HAVING子句在应用GROUP BY之后过滤数据.通常，这意味着任何非聚合过滤器都可以出现在任一位置，但是如果您的查询中没有引用列，则只能在WHERE子句中对其进行过滤.

The WHERE clause filters data from the source before aggregates, whereas HAVING clause filters data after the GROUP BY has been applied. Generally this means any non-aggregate filter can appear in either place, but if you have a column that is not referenced in your query, you can only filter it in a WHERE clause.

例如，如果您有下表:

| ID | VALUE |
--------------
|  1 |    15 |
|  2 |    15 |
|  3 |    20 |
|  4 |    20 |
|  5 |    25 |
|  6 |    30 |
|  7 |    40 |

假设您要应用以下查询:

Suppose you wanted to apply the following query:

select value, count(value)
from Table1
group by value

但是您只想在ID > 2处包括行.如果将其放在HAVING子句中，则会收到 错误 ，因为ID列在汇总后不可用，因为它不在SELECT子句中.在这种情况下，您将需要使用WHERE子句:

But you only wanted to include rows where ID > 2. If you put that in a HAVING clause, you will get an error, because the ID column is not available post aggregate as it is not in the SELECT clause. In that case, you would be required to use a WHERE clause instead:

select value, count(value)
from Table1
where id > 2
group by value

演示: http://www.sqlfiddle.com/#!2/f6741/16

这篇关于为什么有“有"时为什么有“哪里"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！