mysql - 当没有 "having"子句时， "where"和 "group by"是否完全等价？

Question

通常，having 和 where 子句在没有 group by 子句时有什么区别吗(特别是在 MySQL、PostrgrSQL、Oracle 中和 MSSQL)？

select * from user where foo > 3似乎等同于从拥有 foo > 3 的用户中选择 *

Answer 1

至少有一个巨大的区别:

HAVING 子句将不使用索引:

explain select username from users having user_id = 32654456;
+----+-------------+--------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows   | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+--------+-------------+
|  1 | SIMPLE      | users | index | NULL          | PRIMARY | 32      | NULL | 661107 | Using index |
+----+-------------+--------+-------+---------------+---------+---------+------+--------+-------------+

WHERE 子句将:

explain select username from users where user_id = 32654456;
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | users | const | PRIMARY       | PRIMARY | 32      | const |    1 | Using index |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------------+