通过与 Jira 对比,让您更全面了解 PingCode

  • 首页
  • 需求与产品管理
  • 项目管理
  • 测试与缺陷管理
  • 知识管理
  • 效能度量
        • 更多产品

          客户为中心的产品管理工具

          专业的软件研发项目管理工具

          简单易用的团队知识库管理

          可量化的研发效能度量工具

          测试用例维护与计划执行

          以团队为中心的协作沟通

          研发工作流自动化工具

          账号认证与安全管理工具

          Why PingCode
          为什么选择 PingCode ?

          6000+企业信赖之选,为研发团队降本增效

        • 行业解决方案
          先进制造(即将上线)
        • 解决方案1
        • 解决方案2
  • Jira替代方案

25人以下免费

目录

在mysql中既然where和having都能过滤,为什么用where的多

SQL 提供了多种对数据进行过滤的方式,包括WHERE、HAVING以及ON子句等。虽然它们都能够实现类似的功能,但是它们之间存在一些的区别。WHERE与HAVING的根本区别在于:WHERE子句在GROUP BY分组和聚合函数之前对数据行进行过滤。

一、where和having都能过滤,为什么用where的多

SQL 提供了多种对数据进行过滤的方式,包括WHERE、HAVING以及ON子句等。虽然它们都能够实现类似的功能,但是它们之间存在一些的区别。

WHERE 与 HAVING

WHERE与HAVING的根本区别在于:

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

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

— 查找人数大于 5 的部门

select dept_id, count(*)

from employee

where count(*) > 5

group by dept_id;

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

— 查找人数大于 5 的部门

select dept_id, count(*)

from employee

group by dept_id

having count(*) > 5;

dept_id|count(*)|

——-|——–|

      4|       9|

      5|       8|

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

— 统计每个部门月薪大于等于 30000 的员工人数

select dept_id, count(*)

from employee

group by dept_id

having salary >= 30000;

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

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

— 语句 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可以组合在一起使用。例如:

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

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中指定其他过滤条件。上面语句不但结果相同,数据库的执行计划也相同。以上语句的执行计划如下:

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 的写法。

对于外连接而言,连接条件只能用ON子句表示,因为WHERE子句无法表示外连接的语义。例如:

select d.dept_name, e.emp_name, e.sex, e.salary

from department d

left join employee e on (e.dept_id = d.dept_id)

where d.dept_name = ‘保卫部’;

dept_name|emp_name|sex|salary|

———|——–|—|——|

保卫部   |        |   |      |

由于“保卫部”没有员工,我们需要使用外连接返回部门的信息;WHERE条件用于过滤 dept_id = 6 的数据;此时,员工表中返回的都是 NULL。

对于以上语句,如果将WHERE子句中的过滤条件放到ON子句中,结果将会完全不同:

select d.dept_name, e.emp_name, e.sex, e.salary

from department d

left join employee e on (e.dept_id = d.dept_id and d.dept_name = ‘保卫部’);

dept_name|emp_name|sex|salary|

———|——–|—|——|

行政管理部|        |   |      |

人力资源部|        |   |      |

财务部   |        |   |      |

研发部   |        |   |      |

销售部   |        |   |      |

保卫部   |        |   |      |

左外连接返回了所有的部门信息,而且员工信息都为 NULL;显然,这不是我们期望的结果。我们可以通过执行计划分析一下为什么会这样:

explain analyze

select d.dept_name, e.emp_name, e.sex, e.salary

from department d

left join employee e on (e.dept_id = d.dept_id and d.dept_name = ‘保卫部’);

-> Nested loop left join  (cost=7.60 rows=30) (actual time=0.098..0.278 rows=6 loops=1)

    -> Table scan on d  (cost=0.85 rows=6) (actual time=0.052..0.057 rows=6 loops=1)

    -> Filter: (d.dept_name = ‘保卫部’)  (cost=0.71 rows=5) (actual time=0.035..0.035 rows=0 loops=6)

        -> Index lookup on e using idx_emp_dept (dept_id=d.dept_id)  (cost=0.71 rows=5) (actual time=0.020..0.032 rows=4 loops=6)

查询计划显示使用 Nested loop left join 方式执行连接操作;对于 department 使用全表扫描的方式返回 6 行记录;对于 employee 表采用索引(idx_emp_dept)查找,同时使用“d.dept_name = ‘保卫部’”作为过滤条件,循环 6 次返回了 0 行记录;最终返回了上面的结果。

作为对比,我们可以看看将过滤条件放到WHERE子句时的执行计划:

explain analyze

select d.dept_name, e.emp_name, e.sex, e.salary

from department d

left join employee e on (e.dept_id = d.dept_id)

where d.dept_name = ‘保卫部’;

-> Nested loop left join  (cost=1.98 rows=5) (actual time=0.074..0.078 rows=1 loops=1)

    -> Filter: (d.dept_name = ‘保卫部’)  (cost=0.85 rows=1) (actual time=0.049..0.053 rows=1 loops=1)

        -> Table scan on d  (cost=0.85 rows=6) (actual time=0.039..0.047 rows=6 loops=1)

    -> Index lookup on e using idx_emp_dept (dept_id=d.dept_id)  (cost=1.12 rows=5) (actual time=0.021..0.021 rows=0 loops=1)

查询计划显示使用 Nested loop left join 方式执行连接操作;对于 department 通过扫描返回 1 行记录(d.dept_name = ‘保卫部’);对于 employee 表采用索引(idx_emp_dept)查找,同时使用 dept_id=d.dept_id 作为过滤条件,循环 1 次返回了 0 行记录。

我们再看一个外连接的示例:

select d.dept_name, e.emp_name, e.sex, e.salary

from department d

left join employee e on (e.dept_id = d.dept_id and e.emp_name = ‘赵云’);

dept_name |emp_name|sex|salary  |

———-|——–|—|——–|

行政管理部|        |   |        |

人力资源部|        |   |        |

财务部    |        |   |        |

研发部    |赵云    |男 |15000.00|

销售部    |        |   |        |

保卫部    |        |   |        |

select d.dept_name, e.emp_name, e.sex, e.salary

from department d

left join employee e on (e.dept_id = d.dept_id)

where e.emp_name = ‘赵云’;

dept_name|emp_name|sex|salary  |

———|——–|—|——–|

研发部   |赵云    |男 |15000.00|

名列前茅个查询语句返回了所有的部门信息,以及部门中名叫“赵云”的员工;第二个查询实际上等价于内连接查询。

一般来说,对于左外连接查询,左表的过滤应该使用WHERE子句,右表的过滤应该使用ON子句;右外连接查询正好相反;全外连接的过滤条件使用ON子句。

延伸阅读:

二、resultMap 知识点

resultMap 元素用来描述如何将结果集映射到 Java 对象,使用 resultMap 对列表展示所需的必要字段来进行自动映射,特别是当数据库的字段名和实体类 POJO 中的属性名不一致的情况下,比如角色名称,字段名/列名 column 是 roleName,而 User 对象的属性名则为 userRoleName ,此时就需要做映射。

resultMap 元素的属性值和子节点

id 属性:少数标识,此 id 值用于 select 元素 resultMap 属性的引用。

type 属性:表示该 resultMap 的映射结果类型。

result 子节点:用于标识一些简单属性,其中 column 属性表示从数据库中查询的字段名或别名, property 属性则表示查询出来的字段对应的值赋给实体对象的哪个属性。

说明:MyBatis 中在对查询进行 select 映射的时候,返回类型可以用 resultType 也可以用 resultMap ,resultType和 resultMap 有一定关联和区别,应用场景也不同。

相关文章