Mysql中having与where的区别小结_Mysql

一. 简介

where

对查询数据进行过滤

having

用于对已分组的数据进行过滤【having和group by 必须配合使用（有having必须出现group by）】

二. 用法

where

select * from table where sum(字段)>100

having

select * from table group by 字段 having 字段>10

三.区别

1. 被执行的数据来源不同

where是数据从磁盘读入内存的时候进行判断，【数据分组前进行过滤】

而having是磁盘读入内存后再判断。【对分组之后的数据再进行过滤】

所以：使用where比用having效率要高很多。

2. 执行顺序不一样

where>group by>having

mysql解释sql语言时的执行顺序：

select
distinct <select_list>
from <left_table>
<join_type> join <right_table>
on <join_condition><strong>
where</strong> <where_condition>
group by <group_by_list><strong>
having</strong> <having_condition>
order by <order_by_condition>
limit <limit_number>

3. where不可以使用字段的别名，但是having可以

select name as aa from student where aa > 100 (错误)
select name as aa from student group name having  aa > 100 (正确)

4. having能够使用聚合函数当做条件，但是where不能使用，where只能使用存在的列当做条件

select  *  as aa from student where count(*) > 1 (错误)
select *  from student group name having  count(name) > 1 (正确)

注意：能用where就用where

5. 多表关联查询时，where先筛选再联接，having先联接再筛选

找出所有在'it'部门且薪水高于10000的员工：（在联接之前先进行了筛选）

select e.employee_name, d.department_name  
from employees e  
join departments d on e.department_id = d.department_id  
where e.salary > 10000 and d.department_name = 'it';

找出每个客户下订单的总金额超过1000的客户及其订单总金额：（先联表，基于分组后的聚合结果来过滤）

select c.customer_name, sum(o.order_amount) as total_amount  
from customers c  
join orders o on c.customer_id = o.customer_id  
group by c.customer_name  
having sum(o.order_amount) > 1000;