WHERE与HAVING与ON

1. where与having的区别

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

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

    • having 是一个过滤声明
    • 在查询结果返回结果集以后,对结果进行的过滤操作;
    • having 中可以使用聚合函数。
    • 所以可以看出, having 通常和聚合函数以及 group by 连用。(面试知识点)
    • having子句中不能输用除了分组字段和聚合函数之外的其他字段。

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

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

当然, WHEREhaving 可以组合在一起使用。例如:

sql
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)查询, WHEREON 中的过滤条件等效;
  • 对于外链接(outer join)查询, ON 中的过滤条件在连接操作之前执行, WHERE 中的过滤条件(逻辑上)在连接操作之后执行。

2. 聚合函数和group by

聚合函数就是例如:

SUM、COUNT、MAX、AVG等

对一组(多条)数据操作的函数,需要配合group by来使用。

sql
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中可以使用聚合函数

sql
1
2
3
4
5
#筛选出ABC及各区学校数量
SELECT region,count(school)
FROM T02_beijing_school
WHERE region IN ('A','B','C')
GROUP BY region HAVING COUNT(school) > 10;
sql
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;