当'有'[重复]时，为什么你有'哪里'？

This question already has an answer here:

这个问题在这里已有答案:

SQL - having VS where 7 answers

SQL - 有VS的7个答案

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?

我知道这个问题有很多讨论,但我的研究都没有让我相信MySQL中'where'和'having'子句之间的区别。根据我的理解,我们可以使用'having'实现'where'子句可以完成的所有操作。例如。从具有username ='admin'的用户中选择*。那为什么你需要'where'条款?使用哪里可以产生任何性能差异?

4 个解决方案

#1

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.

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

For example, if you have the following table:

例如,如果您有以下表格:

| 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

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:

但是您只想包含ID> 2的行。如果将其放在HAVING子句中,则会出现错误,因为ID列在聚合后不可用,因为它不在SELECT子句中。在这种情况下,您将需要使用WHERE子句:

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

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

#2

The difference between HAVING from WHERE clause is that HAVING supports aggregated columns while WHERE doesn't because it is only applicable for individual rows., EG

来自WHERE子句的HAVING之间的区别在于HAVING支持聚合列而WHERE不支持聚合列,因为它仅适用于单个行。

SELECT ID
FROM tableName
GROUP BY ID
HAVING COUNT(ID) > 1  --- <<== HERE

From the MySQL docs,

从MySQL文档中,

"You may use Alias's if you use HAVING instead of WHERE this is one of the defined differences between the two clauses. Having is also slower and will not be optimized, but if you are placing a complex function like this in your where you obviously aren't expecting great speed."

“你可以使用Alias,如果你使用HAVING而不是WHERE,这是两个子句之间定义的差异之一。拥有也更慢并且不会被优化,但是如果你在你明显没有的地方放置这样的复杂函数期待很快的速度。“

#3

Where evaluates on the single row level, whereas having is used for group by expressions.

其中,在单行级别上进行求值,而having则用于按表达式进行分组。

#4

With the HAVING clause, you can specify a condition to filter groups as opposed to filtering individual rows, which happens in the WHERE phase.

Only groups for which the logical expression in the HAVING clause evaluates to TRUE are returned by the HAVING phase . Groups for which the logical expression evaluates to FALSE or UNKNOWN are filtered out.

使用HAVING子句,您可以指定过滤组的条件,而不是过滤单个行,这在WHERE阶段中发生。 HAVING阶段仅返回HAVING子句中的逻辑表达式求值为TRUE的组。过滤掉逻辑表达式求值为FALSE或UNKNOWN的组。

When GROUP BY is not used, HAVING behaves like a WHERE clause . Regarding performance comparison please see this article

不使用GROUP BY时,HAVING的行为类似于WHERE子句。关于性能比较,请参阅此文章

#1