WHERE与HAVING与ON

1. where与having的区别

WHERE 与 HAVING 的根本区别在于： WHERE子句在 GROUP BY 分组与聚合函数之前对数据行进行过滤； HAVING 子句对 GROUP BY 分组和聚合函数之后的数据进行过滤。

  1. where
    • where是一个约束声明，使用where来约束来数据库的数据；
    • where是在结果返回之前起作用的；
    • where子句中不能使用聚合函数。

  2. having

    • having 是一个过滤声明
    • 在查询结果返回结果集以后，对结果进行的过滤操作；
    • 在 having 中可以使用聚合函数。
    • 所以可以看出， having 通常和聚合函数以及 group by 连用。（面试知识点）

    • having子句中不能输用除了分组字段和聚合函数之外的其他字段。

      1
      2
      3
      4
      5
      6
      #例如:查找每个部门月薪
      #大于等于30000的员工人数
      select dept_id,count(*)
      from employee
      group by dept_id
      having salary >= 30000;

因为经过group by分组和聚合函数之后，不再存在salary字段，having 子句中只能使用分组字段或者聚合函数。

当然， WHERE 和 having 可以组合在一起使用。例如：

1
2
3
4
5
6
7
8
9
10
11
select dept_id, count(*)
from employee
where salary > 10000
group by dept_id
having count(*) > 1;
/*
该语句返回了月薪大于10000的员工人数
大于1的部门；WHERE用于过滤月薪大于
10000的员工；HAVING用于过滤员工数量
大于1的部门。
*/
  1. WHERE与ON

当涉及多个表的关联时，我们那既可以使用 WHERE 子句也可以使用 ON 子句指定链接条件和过滤条件。这两者的主要区别在于：

  • 对于内链接（inner join）查询， WHERE 和 ON 中的过滤条件等效；
  • 对于外链接（outer join）查询， ON 中的过滤条件在连接操作之前执行， WHERE 中的过滤条件（逻辑上）在连接操作之后执行。

2. 聚合函数和group by

聚合函数就是例如：

SUM、COUNT、MAX、AVG等

对一组（多条）数据操作的函数，需要配合group by来使用。

1
2
3
4
#如：
select SUM(popultion),region 
FROM T01_beijing 
GROUP BY region;//计算北京每个分区的人数

3. where和having的执行顺序

  • where 早于 group by 早于 having
  • where子句在聚合前筛选记录，也就是说作用在group by子句和having子句前，而having子句在聚合后对组记录进行筛选

4. where不能使用聚合函数、having中可以使用聚合函数

1
2
3
4
5
#筛选出ABC及各区学校数量
SELECT region，count(school)
FROM T02_beijing_school
WHERE region IN ('Ａ','B','C')
GROUP BY region HAVING COUNT(school) > 10;
1
2
3
4
5
#筛选出ABC三地中学校数量超过10的区及各区学校
SELECT region ,count(school)
FROM T02_beijing_school
WHERE region IN ('A','B','C')
GROUP BY region HAVING count(shool) > 10;