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

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

WHERE 与 HAVING

WHERE与HAVING的根本区别在于：

因此，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子句指定连接条件和过滤条件。这两者之间的主要区别在于：

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

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

上一篇

CSS3有哪些特性?

下一篇

Django项目中如何配置MySQL数据库?