SQL HAVING 和 WHERE 和有什么不一样？

What's the difference between HAVING and WHERE?

提问by ColinYounger

I must be googling in the wrong way or I'm having a stupid moment in time.

我一定是用错误的方式在谷歌上搜索，或者我有一个愚蠢的时刻。

What's the difference between HAVINGand WHEREin an SQL SELECTstatement?

HAVING和WHEREin和in 有什么区别SQL SELECT？

EDIT: I've marked Steven's answer as the correct one as it contained the key bit of information on the link:

编辑：我已将史蒂文的答案标记为正确答案，因为它包含链接上的关键信息：

When GROUP BYis not used, HAVINGbehaves like a WHEREclause

当GROUP BY不使用时，HAVING表现得像一个WHERE子句

The situation I had seen the WHEREin did not have GROUP BYand is where my confusion started. Of course, until you know this you can't specify it in the question.

我所看到的WHERE情况并没有GROUP BY，这就是我开始困惑的地方。当然，除非您知道这一点，否则您无法在问题中指定它。

Many thanks for all the answers which were very enlightening.

非常感谢所有非常有启发性的答案。

采纳答案by Steven

HAVING specifies a search condition for a group or an aggregate function used in SELECT statement.

HAVING 为 SELECT 语句中使用的组或聚合函数指定搜索条件。

Source

来源

回答by wcm

HAVING: is used to check conditions afterthe aggregation takes place.
WHERE: is used to check conditions beforethe aggregation takes place.

HAVING：用于在聚合发生后检查条件。
WHERE：用于在聚合发生之前检查条件。

This code:

这段代码：

select City, CNT=Count(1)
From Address
Where State = 'MA'
Group By City

Gives you a table of all cities in MA and the number of addresses in each city.

为您提供 MA 中所有城市的表格以及每个城市的地址数量。

This code:

这段代码：

select City, CNT=Count(1)
From Address
Where State = 'MA'
Group By City
Having Count(1)>5

Gives you a table of cities in MA with more than 5 addresses and the number of addresses in each city.

为您提供 MA 中超过 5 个地址的城市表以及每个城市的地址数。

回答by onedaywhen

Number one difference for me: if HAVINGwas removed from the SQL language then life would go on more or less as before. Certainly, a minority queries would need to be rewritten using a derived table, CTE, etc but they would arguably be easier to understand and maintain as a result. Maybe vendors' optimizer code would need to be rewritten to account for this, again an opportunity for improvement within the industry.

对我来说，第一个区别是：如果HAVING从 SQL 语言中删除，那么生活或多或少会像以前一样继续。当然，少数查询需要使用派生表、CTE 等重写，但可以说因此它们更容易理解和维护。也许供应商的优化器代码需要重写以解决这一问题，这也是行业内改进的机会。

Now consider for a moment removing WHEREfrom the language. This time the majorityof queries in existence would need to be rewritten without an obvious alternative construct. Coders would have to get creative e.g. inner join to a table known to contain exactly one row (e.g. DUALin Oracle) using the ONclause to simulate the prior WHEREclause. Such constructions would be contrived; it would be obvious there was something was missing from the language and the situation would be worse as a result.

现在考虑一下WHERE从语言中删除。这一次，现有的大多数查询都需要在没有明显替代结构的情况下进行重写。编码人员必须具有创造性，例如DUAL使用该ON子句模拟先前WHERE子句，内部连接到已知仅包含一行（例如在 Oracle 中）的表。这种结构是人为的；很明显，语言中缺少某些东西，因此情况会更糟。

TL;DR we could lose HAVINGtomorrow and things would be no worse, possibly better, but the same cannot be said of WHERE.

TL;DR我们HAVING明天可能会输，事情不会更糟，可能会更好，但不能这样说WHERE。

---

---

From the answers here, it seems that many folk don't realize that a HAVINGclause may be used without a GROUP BYclause. In this case, the HAVINGclause is applied to the entire table expression and requires that only constants appear in the SELECTclause. Typically the HAVINGclause will involve aggregates.

从这里的答案来看，似乎很多人没有意识到HAVING可以在没有GROUP BY子句的情况下使用子句。在这种情况下，该HAVING子句应用于整个表表达式，并且要求该SELECT子句中只出现常量。通常，该HAVING子句将涉及聚合。

This is more useful than it sounds. For example, consider this query to test whether the namecolumn is unique for all values in T:

这比听起来更有用。例如，考虑此查询以测试name列对于 中的所有值是否唯一T：

SELECT 1 AS result
  FROM T
HAVING COUNT( DISTINCT name ) = COUNT( name );

There are only two possible results: if the HAVINGclause is true then the result with be a single row containing the value 1, otherwise the result will be the empty set.

只有两种可能的结果：如果HAVING子句为真，则结果是包含 value 的单行1，否则结果将是空集。

回答by Kaiser Advisor

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

HAVING 子句被添加到 SQL 中，因为 WHERE 关键字不能与聚合函数一起使用。

Check out this w3schools linkfor more information

查看此w3schools 链接以获取更多信息

Syntax:

句法：

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

---

---

A query such as this:

像这样的查询：

SELECT column_name, COUNT( column_name ) AS column_name_tally
  FROM table_name
 WHERE column_name < 3
 GROUP 
    BY column_name
HAVING COUNT( column_name ) >= 3;

...may be rewritten using a derived table (and omitting the HAVING) like this:

...可以使用派生表（并省略HAVING）重写，如下所示：

SELECT column_name, column_name_tally
  FROM (
        SELECT column_name, COUNT(column_name) AS column_name_tally
          FROM table_name
         WHERE column_name < 3
         GROUP 
            BY column_name
       ) pointless_range_variable_required_here
 WHERE column_name_tally >= 3;

回答by Paul Sweatte

The difference between the two is in the relationship to the GROUP BY clause:

两者的区别在于与 GROUP BY 子句的关系：

• WHERE comes before GROUP BY; SQL evaluates the WHERE clause before it groups records.

• HAVING comes after GROUP BY; SQL evaluates HAVING after it groups records.

• WHERE 位于 GROUP BY 之前；SQL 在对记录进行分组之前评估 WHERE 子句。

• HAVING 在 GROUP BY 之后；SQL 在对记录进行分组后评估 HAVING。

References

参考

• SQLite SELECT Statement Syntax/Railroad Diagram

• Informix SELECT Statement Syntax/Railroad Diagram

• SQLite SELECT 语句语法/铁路图

• Informix SELECT 语句语法/铁路图

回答by Galwegian

HAVINGis used when you are using an aggregate such as GROUP BY.

HAVING当您使用诸如GROUP BY.

SELECT edc_country, COUNT(*)
FROM Ed_Centers
GROUP BY edc_country
HAVING COUNT(*) > 1
ORDER BY edc_country;

回答by davidcl

WHERE is applied as a limitation on the set returned by SQL; it uses SQL's built-in set oeprations and indexes and therefore is the fastest way to filter result sets. Always use WHERE whenever possible.

WHERE 用于限制 SQL 返回的集合；它使用 SQL 的内置集合操作和索引，因此是过滤结果集的最快方法。尽可能始终使用 WHERE。

HAVING is necessary for some aggregate filters. It filters the query AFTER sql has retrieved, assembled, and sorted the results. Therefore, it is much slower than WHERE and should be avoided except in those situations that require it.

某些聚合过滤器需要 HAVING。它在 sql 检索、组合和排序结果后过滤查询。因此，它比 WHERE 慢得多，应该避免，除非在那些需要它的情况下。

SQL Server will let you get away with using HAVING even when WHERE would be much faster. Don't do it.

即使在 WHERE 会快得多的情况下，SQL Server 也会让您使用 HAVING。不要这样做。

回答by Achilles Ram Nakirekanti

WHERE clause does not work for aggregate functions
means : you should not use like this bonus : table name

WHERE 子句不适用于聚合函数
意味着：您不应该像这样使用奖励：表名

SELECT name  
FROM bonus  
GROUP BY name  
WHERE sum(salary) > 200  

HERE Instead of using WHERE clause you have to use HAVING..

HERE 而不是使用 WHERE 子句，您必须使用 HAVING..

without using GROUP BY clause, HAVING clause just works as WHERE clause

不使用 GROUP BY 子句，HAVING 子句仅用作 WHERE 子句

SELECT name  
FROM bonus  
GROUP BY name  
HAVING sum(salary) > 200  

回答by M Asad Ali

Difference b/w WHEREand HAVINGclause:

区别黑白WHERE和HAVING条款：

The main difference between WHEREand HAVINGclause is, WHEREis used for row operations and HAVINGis used for column operations.

WHEREandHAVING子句的主要区别是，WHERE用于行操作，HAVING用于列操作。

Why we need HAVINGclause?

为什么我们需要HAVING条款？

As we know, aggregate functions can only be performed on columns, so we can not use aggregate functions in WHEREclause. Therefore, we use aggregate functions in HAVINGclause.

众所周知，聚合函数只能对列执行，因此不能在WHERE子句中使用聚合函数。因此，我们在HAVING子句中使用聚合函数。

回答by bebosh

When GROUP BYis not used, the WHEREand HAVINGclauses are essentially equivalent.

当GROUP BY不使用时，WHEREandHAVING子句本质上是等价的。

However, when GROUP BYis used:

但是，何时GROUP BY使用：

• The WHEREclause is used to filter records from a result. The filtering occurs before any groupings are made.
• The HAVINGclause is used to filter values from a group (i.e., to check conditions after aggregation into groups has been performed).

• 该WHERE子句用于从结果中过滤记录。过滤发生在进行任何分组之前。
• 该HAVING子句用于从组中过滤值（即，在执行聚合到组后检查条件）。

Resource from Here

资源从这里