MySql - HAVING vs WHERE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16155937/
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
MySql - HAVING vs WHERE
提问by Noam B.
What is the difference between these 2 queries?
这两个查询有什么区别？
SELECT f.name,
u.name,
u.id
FROM families f
JOIN units u
ON f.unit_id = u.id
HAVING u.id IN( 43, 413, 22 )
And:
和：
SELECT f.name,
u.name,
u.id
FROM families f
JOIN units u
ON f.unit_id = u.id
WHERE u.id IN( 43, 413, 22 )
The result of these 2 queries is exactly the same. So where is the difference?
这两个查询的结果完全相同。那么区别在哪里呢？
回答by Barmar
WHEREis used to select data in the original tables being processed.
WHERE用于选择正在处理的原始表中的数据。
HAVINGis used to filter data in the result set that was produced by the query. This means it can reference aggregate values and aliases in the SELECTclause.
HAVING用于过滤查询生成的结果集中的数据。这意味着它可以在SELECT子句中引用聚合值和别名。
For instance, can write:
例如，可以写：
SELECT t1.val - t2.val diff
FROM t1 JOIN t2 ON (some expression)
HAVING diff > 10
This wouldn't work using WHEREbecause diffis an alias, not one of the original table columns. You could write instead:
这不起作用，WHERE因为diff它是别名，而不是原始表列之一。你可以写：
SELECT t1.val - t2.val diff
FROM t1 JOIN t2 ON (some expression)
WHERE t1.val - t2.val > 10
but then it may have to do all the subtractions twice: once for selecting, and again to produce the result set.
但随后它可能必须进行两次所有的减法运算：一次用于选择，另一次用于生成结果集。
回答by Ramesh Rajendran
Difference between the having and where clause in sql is that the where clause can not be used with aggregates, but the having clause can. One way to think of it is that the having clause is an additional filter to the where clause.
sql中have和where子句的区别在于where子句不能与聚合一起使用，而have子句可以。一种思考方式是，have 子句是 where 子句的附加过滤器。
Which is better : click
哪个更好：点击
回答by Mike Perrenoud
In these queries, nothing.But if you were to use a GROUP BYyou would see a difference. If you were to use a GROUP BYthe HAVINGwould be applied to the group whereas the WHEREwould be applied to the SELECTbefore grouping the data.
在这些查询中，什么都没有。但是，如果您使用 a ，GROUP BY您会发现有所不同。如果你使用GROUP BY的HAVING将被应用到组，而WHERE将被应用到SELECT分组数据之前。
