最核心的区别:
WHERE是针对表的(原始)数据行做筛选,而HAVING是对结果集做筛选。
通过以下两点说明。
1. 执行效率对比
2. 可获取的数据差别(验证where筛选table的row,而having筛选result set的row)SELECT ownerUserId, sum(amount) FROM PayOrder WHERE ownerUserId =7744 GROUP BY ownerUserId HAVING SUM(amount) > 100000000
因为可以使用ownerUserId的索引,sql执行时间 0.007秒。把条件ownerUserId = 7744放到HAVING后SELECT ownerUserId, sum(amount) FROM PayOrder GROUP BY ownerUserId HAVING SUM(amount) > 100000000 AND ownerUserId =7744
索引在select阶段不再生效,sql执行时间 13.797秒。当前PayOrder一共有记录3997178条,而ownerUserId = 7744放到HAVING后,需要扫描所有的row
(使用HAVING不一定要同时使用GROUP BY)执行SELECT amount FROM PayOrder HAVING id = 10000
报错[Err] 1054 - Unknown column 'id' in 'having clause'因为结果集里,没有id这一列。
而执行SELECT amount FROM PayOrder WHERE id = 10000
是没问题的,因为table的每个row,都有id这一列