SQL:WHERE、ON、HAVING的区别

评论

WHERE 与 HAVING

WHERE与HAVING的根本区别在于：

  • WHERE子句在GROUP BY分组和聚合函数之前对数据行进行过滤；
  • HAVING子句对GROUP BY分组和聚合函数之后的数据行进行过滤。

因此，WHERE子句中不能使用聚合函数。例如，以下语句将会返回错误：

1
2
3
4
5
-- 查找人数大于 5 的部门
select dept_id, count(*)
from employee
where count(*) > 5
group by dept_id;

由于在执行WHERE子句时，还没有计算聚合函数 count(*)，所以无法使用。正确的方法是使用HAVING对聚合之后的结果进行过滤：

1
2
3
4
5
6
7
8
9
-- 查找人数大于 5 的部门
select dept_id, count(*)
from employee
group by dept_id
having count(*) > 5;
dept_id|count(*)|
-------|--------|
      4|       9|
      5|       8|

另一方面，HAVING子句中不能使用除了分组字段和聚合函数之外的其他字段。例如，以下语句将会返回错误：

1
2
3
4
5
-- 统计每个部门月薪大于等于 30000 的员工人数
select dept_id, count(*)
from employee
group by dept_id
having salary >= 30000;

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

SQLite 虽然允许HAVING子句中出现其他字段，但是得到的结果不正确。

从性能的角度来说，HAVING子句中如果使用了分组字段作为过滤条件，应该替换成WHERE子句；因为WHERE可以在执行分组操作和计算聚合函数之前过滤掉不需要的数据，性能会更好。下面示例中的语句 1 应该替换成语句 2：

1
2
3
4
5
6
7
8
9
10
11
-- 语句 1
select dept_id, count(*)
from employee
group by dept_id
having dept_id = 1;

-- 语句 2
select dept_id, count(*)
from employee
where dept_id = 1
group by dept_id;

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

1
2
3
4
5
6
7
8
select dept_id, count(*)
from employee
where salary > 10000
group by dept_id
having count(*) > 1;
dept_id|count(*)|
-------|--------|
      1|       3|

该语句返回了月薪大于 10000 的员工人数大于 1 的部门；WHERE用于过滤月薪大于 10000 的员工；HAVING用于过滤员工数量大于 1 的部门。

WHERE 与 ON

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

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

对于内连接查询而言，以下三个语句的结果相同：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- 语句 1
select d.dept_name, e.emp_name, e.sex, e.salary 
from employee e, department d
where e.dept_id = d.dept_id
and e.emp_id = 10;
dept_name|emp_name|sex|salary |
---------|--------|---|-------|
研发部   |廖化    |男  |6500.00|

-- 语句 2
select d.dept_name, e.emp_name, e.sex, e.salary 
from employee e
join department d on (e.dept_id = d.dept_id and e.emp_id = 10);
dept_name|emp_name|sex|salary |
---------|--------|---|-------|
研发部   |廖化    |男  |6500.00|

-- 语句 3
select d.dept_name, e.emp_name, e.sex, e.salary 
from employee e
join department d on (e.dept_id = d.dept_id)
where e.emp_id = 10;
dept_name|emp_name|sex|salary |
---------|--------|---|-------|
研发部   |廖化    |男  |6500.00|

语句 1 在WHERE中指定连接条件和过滤条件；语句 2 在ON中指定连接条件和过滤条件；语句 3 在ON中指定连接条件，在WHERE中指定其他过滤条件。上面语句不但结果相同，数据库的执行计划也相同。以 MySQL 为例，以上语句的执行计划如下：

1
2
3
4
id|select_type|table|partitions|type |possible_keys       |key    |key_len|ref  |rows|filtered|Extra|
--|-----------|-----|----------|-----|--------------------|-------|-------|-----|----|--------|-----|
 1|SIMPLE     |e    |          |const|PRIMARY,idx_emp_dept|PRIMARY|4      |const|   1|     100|     |
 1|SIMPLE     |d    |          |const|PRIMARY             |PRIMARY|4      |const|   1|     100|     |

尽管如此，仍然建议将两个表的连接条件放在ON子句中，将其他过滤条件放在WHERE子句中；这样语义更加明确，更容易阅读和理解。对于上面的示例而言，推荐使用语句 3 的写法。

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

在使用 left join 时，on 和 where 条件的区别如下：

1、on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录。

2、where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

假设有两张表：

表1：tab1

1
2
3
4
id size
1  10
2  20
3  30

表2：tab2

1
2
3
4
size name
10   AAA
20   BBB
20   CCC

两条SQL:

1
2
1、select * from tab1 left join tab2 on tab1.size = tab2.size where tab2.name='AAA'
2、select * from tab1 left join tab2 on tab1.size = tab2.size and tab2.name='AAA'

第一条SQL的过程：

1、中间表

on 条件:

1
2
3
4
5
6
tab1.size = tab2.size
tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA
2 20 20 BBB
2 20 20 CCC
3 30 (null) (null)

2、再对中间表过滤

where 条件：

tab2.name=’AAA’

1
2
tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA

第二条SQL的过程：

1、中间表

on 条件:

1
2
3
4
5
tab1.size = tab2.size and tab2.name='AAA'
(条件不为真也会返回左表中的记录) tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA
2 20 (null) (null)
3 30 (null) (null)

其实以上结果的关键原因就是 left join,right join,full join 的特殊性。

不管 on 上的条件是否为真都会返回 left 或 right 表中的记录，full 则具有 left 和 right 的特性的并集。

而 inner jion 没这个特殊性，则条件放在 on 中和 where 中，返回的结果集是相同的。

补充

  • SQL标准要求HAVING必须仅引用GROUP BY子句中的列或聚合函数中使用的列。 但是，MySQL支持对此行为的扩展，并允许HAVING引用SELECT列表中的列和外部子查询中的列。

    如果HAVING子句引用了不明确的列，则会出现警告。在下面的语句中，col2不明确，因为它同时用作别名和列名:

    1
    SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;

    优先考虑标准SQL行为，因此如果HAVING使用的列名同时出现在GROUP BY和输出列列表使用的别名中，则会优先选择GROUP BY列中的列名。

  • 不要对应该出现在WHERE子句中的项使用HAVING。例如，不要写下面的内容

    1
    SELECT col_name FROM tbl_name HAVING col_name > 0;

    改为写这个:

    1
    SELECT col_name FROM tbl_name WHERE col_name > 0;

  • HAVING子句可以引用聚合函数，而WHERE子句不能

    1
    SELECT user, MAX(salary) FROM users GROUP BY user HAVING MAX(salary) > 10;

参考链接：

https://blog.csdn.net/horses/article/details/105380420

https://www.runoob.com/w3cnote/sql-different-on-and-where.html

https://www.cnblogs.com/BxScope/p/10859260.html

k12大数据技术

个人简介。