在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 有一定关联和区别，应用场景也不同。

最后建议，企业在引入信息化系统初期，切记要合理有效地运用好工具，这样一来不仅可以让公司业务高效地运行，还能最大程度保证团队目标的达成。同时还能大幅缩短系统开发和部署的时间成本。特别是有特定需求功能需要定制化的企业，可以采用我们公司自研的企业级低代码平台：织信Informat。 织信平台基于数据模型优先的设计理念，提供大量标准化的组件，内置AI助手、组件设计器、自动化（图形化编程）、脚本、工作流引擎（BPMN2.0）、自定义API、表单设计器、权限、仪表盘等功能，能帮助企业构建高度复杂核心的数字化系统。如ERP、MES、CRM、PLM、SCM、WMS、项目管理、流程管理等多个应用场景，全面助力企业落地国产化/信息化/数字化转型战略目标。 版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们微信：Informat_5 处理，核实后本网站将在24小时内删除。