MySQL 哪里 vs 有

WHERE vs HAVING

提问by baloo

Why do you need to place columns you create yourself (for example select 1 as "number") after HAVINGand not WHEREin MySQL?

为什么需要将自己创建的列（例如select 1 as "number"）放在 MySQL之后HAVING而不是WHERE放在 MySQL 中？

And are there any downsides instead of doing WHERE 1(writing the whole definition instead of a column name)?

是否有任何缺点而不是这样做WHERE 1（编写整个定义而不是列名）？

回答by Quassnoi

Why is it that you need to place columns you create yourself (for example "select 1 as number") after HAVING and not WHERE in MySQL?

为什么需要在 HAVING 之后放置自己创建的列（例如“选择 1 作为数字”）而不是 MySQL 中的 WHERE ？

WHEREis applied before GROUP BY, HAVINGis applied after (and can filter on aggregates).

WHERE被施加之前GROUP BY，HAVING被后应用（并且可以在聚集体筛选）。

In general, you can reference aliases in neither of these clauses, but MySQLallows referencing SELECTlevel aliases in GROUP BY, ORDER BYand HAVING.

通常，您不能在这两个子句中引用别名，但MySQL允许SELECT在GROUP BY、ORDER BY和 中引用级别别名HAVING。

And are there any downsides instead of doing "WHERE 1" (writing the whole definition instead of a column name)

是否有任何缺点而不是执行“WHERE 1”（编写整个定义而不是列名）

If your calculated expression does not contain any aggregates, putting it into the WHEREclause will most probably be more efficient.

如果您的计算表达式不包含任何聚合，将其放入WHERE子句中很可能会更有效。

回答by Fishdrowned

All other answers on this question didn't hit upon the key point.

关于这个问题的所有其他答案都没有触及关键点。

Assume we have a table:

假设我们有一张表：

CREATE TABLE `table` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `value` int(10) unsigned NOT NULL,
 PRIMARY KEY (`id`),
 KEY `value` (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

And have 10 rows with both id and value from 1 to 10:

并且有 10 行，id 和 value 都从 1 到 10：

INSERT INTO `table`(`id`, `value`) VALUES (1, 1),(2, 2),(3, 3),(4, 4),(5, 5),(6, 6),(7, 7),(8, 8),(9, 9),(10, 10);

Try the following 2 queries:

尝试以下 2 个查询：

SELECT `value` v FROM `table` WHERE `value`>5; -- Get 5 rows
SELECT `value` v FROM `table` HAVING `value`>5; -- Get 5 rows

You will get exactly the same results, you can see the HAVING clause can work without GROUP BY clause.

您将获得完全相同的结果，您可以看到 HAVING 子句可以在没有 GROUP BY 子句的情况下工作。

---

---

Here's the difference:

这是区别：

SELECT `value` v FROM `table` WHERE `v`>5;

Error #1054 - Unknown column 'v' in 'where clause'

错误 #1054 - “where 子句”中的未知列“v”

SELECT `value` v FROM `table` HAVING `v`>5; -- Get 5 rows

WHERE clause allows a condition to use any table column, but it cannot use aliases or aggregate functions. HAVING clause allows a condition to use a selected (!) column, alias or an aggregate function.

WHERE 子句允许条件使用任何表列，但不能使用别名或聚合函数。HAVING 子句允许条件使用选定的 (!) 列、别名或聚合函数。

This is because WHERE clause filters data before select, but HAVING clause filters resulting data after select.

这是因为 WHERE 子句在选择之前过滤数据，而 HAVING 子句在选择之后过滤结果数据。

So put the conditions in WHERE clause will be more efficient if you have many many rows in a table.

因此，如果表中有很多行，则将条件放在 WHERE 子句中会更有效。

Try EXPLAIN to see the key difference:

尝试解释以查看主要区别：

EXPLAIN SELECT `value` v FROM `table` WHERE `value`>5;
+----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+
|  1 | SIMPLE      | table | range | value         | value | 4       | NULL |    5 | Using where; Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+

EXPLAIN SELECT `value` v FROM `table` having `value`>5;
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
|  1 | SIMPLE      | table | index | NULL          | value | 4       | NULL |   10 | Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+

You can see either WHERE or HAVING uses index, but the rows are different.

您可以看到 WHERE 或 HAVING 使用索引，但行不同。

回答by David Brunelle

The main difference is that WHEREcannot be used on grouped item (such as SUM(number)) whereas HAVINGcan.

主要区别在于WHERE不能用于分组项目（例如SUM(number)）而HAVING可以。

The reason is the WHEREis done beforethe grouping and HAVINGis done afterthe grouping is done.

原因是在分组之前WHERE完成，在分组HAVING完成之后完成。

回答by Kevin McKelvin

HAVINGis used to filter on aggregations in your GROUP BY.

HAVING用于过滤GROUP BY.

For example, to check for duplicate names:

例如，要检查重复名称：

SELECT Name FROM Usernames
GROUP BY Name
HAVING COUNT(*) > 1

回答by Nuwantha

These 2 will be feel same as first as both are used to say about a condition to filter data. Though we can use ‘having' in place of ‘where' in any case, there are instances when we can't use ‘where' instead of ‘having'. This is because in a select query, ‘where' filters data before ‘select' while ‘having' filter data after ‘select'. So, when we use alias names that are not actually in the database, ‘where' can't identify them but ‘having' can.

这两个与第一个感觉相同，因为两者都用于说明过滤数据的条件。尽管在任何情况下我们都可以使用“have”代替“where”，但在某些情况下，我们不能使用“where”代替“have”。这是因为在选择查询中，'where' 在 'select' 之前过滤数据，而在 'select' 之后过滤数据。因此，当我们使用实际上不在数据库中的别名时，'where' 无法识别它们，但 'have' 可以。

Ex: let the table Student contain student_id,name, birthday,address.Assume birthday is of type date.

例如：让Student表包含student_id、name、birthday、address。假设生日是日期类型。

SELECT * FROM Student WHERE YEAR(birthday)>1993; /*this will work as birthday is in database.if we use having in place of where too this will work*/

SELECT student_id,(YEAR(CurDate())-YEAR(birthday)) AS Age FROM Student HAVING Age>20; 
/*this will not work if we use ‘where' here, ‘where' don't know about age as age is defined in select part.*/

回答by snr

WHEREfilters before data is grouped, and HAVINGfilters after data is grouped. This is an important distinction; rows that are eliminated by a WHEREclause will not be included in the group. This could change the calculated values which, in turn(=as a result) could affect which groups are filtered based on the use of those values in the HAVINGclause.

WHERE在数据分组前过滤，HAVING在数据分组后过滤。这是一个重要的区别; 由WHERE子句消除的行将不包含在组中。这可能会更改计算值，反过来（=作为结果）可能会影响根据HAVING子句中这些值的使用过滤哪些组。

And continues,

并继续，

HAVINGis so similar to WHEREthat most DBMSs treat them as the same thing if no GROUP BYis specified. Nevertheless, you should make that distinction yourself. Use HAVINGonly in conjunction with GROUP BYclauses. Use WHEREfor standard row-level filtering.

HAVING与WHERE非常相似，以至于如果未指定GROUP BY，大多数 DBMS 将它们视为相同的事物。不过，您应该自己进行区分。仅将HAVING与GROUP BY子句结合使用。使用WHERE进行标准的行级过滤。

Excerpt From: Forta, Ben. “Sams Teach Yourself SQL in 10 Minutes (5th Edition) (Sams Teach Yourself...).”.

摘自： Forta，Ben。“Sams Teach Yourself SQL in 10 Minutes (5th Edition) (Sams Teach Yourself...).”。

回答by Hos Mercury

Having is only used with aggregation but where with non aggregation statements If you have where word put it before aggregation (group by)

Have 仅用于聚合，但 where 与非聚合语句一起使用如果您将 where 词放在聚合之前（分组依据）