一、where和having都能過濾，為什么用where的多

SQL 提供了多種對數(shù)據(jù)進行過濾的方式，包括WHERE、HAVING以及ON子句等。雖然它們都能夠?qū)崿F(xiàn)類似的功能，但是它們之間存在一些的區(qū)別。

WHERE 與 HAVING

WHERE與HAVING的根本區(qū)別在于：

因此，WHERE子句中不能使用聚合函數(shù)。例如，以下語句將會返回錯誤：

— 查找人數(shù)大于 5 的部門

select dept_id, count(*)

from employee

where count(*) > 5

group by dept_id;

由于在執(zhí)行WHERE子句時，還沒有計算聚合函數(shù) count(*)，所以無法使用。正確的方法是使用HAVING對聚合之后的結(jié)果進行過濾：

— 查找人數(shù)大于 5 的部門

select dept_id, count(*)

from employee

group by dept_id

having count(*) > 5;

dept_id|count(*)|

——-|——–|

????? 4|?????? 9|

??? ??5|?????? 8|

另一方面，HAVING子句中不能使用除了分組字段和聚合函數(shù)之外的其他字段。例如，以下語句將會返回錯誤：

— 統(tǒng)計每個部門月薪大于等于 30000 的員工人數(shù)

select dept_id, count(*)

from employee

group by dept_id

having salary >= 30000;

因為經(jīng)過GROUP BY分組和聚合函數(shù)之后，不再存在 salary 字段，HAVING子句中只能使用分組字段或者聚合函數(shù)。

從性能的角度來說，HAVING子句中如果使用了分組字段作為過濾條件，應(yīng)該替換成WHERE子句；因為WHERE可以在執(zhí)行分組操作和計算聚合函數(shù)之前過濾掉不需要的數(shù)據(jù)，性能會更好。下面示例中的語句 1 應(yīng)該替換成語句 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 的員工人數(shù)大于 1 的部門；WHERE用于過濾月薪大于 10000 的員工；HAVING用于過濾員工數(shù)量大于 1 的部門。

WHERE 與 ON

當查詢涉及多個表的關(guān)聯(lián)時，我們既可以使用WHERE子句也可以使用ON子句指定連接條件和過濾條件。這兩者之間的主要區(qū)別在于：

對于內(nèi)連接查詢而言，以下三個語句的結(jié)果相同：

— 語句 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 |

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

研發(fā)部?? |廖化??? |男? |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 |

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

研發(fā)部?? |廖化??? |男? |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 |

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

研發(fā)部?? |廖化??? |男? |6500.00|

語句 1 在WHERE中指定連接條件和過濾條件；語句 2 在ON中指定連接條件和過濾條件；語句 3 在ON中指定連接條件，在WHERE中指定其他過濾條件。上面語句不但結(jié)果相同，數(shù)據(jù)庫的執(zhí)行計劃也相同。以上語句的執(zhí)行計劃如下：

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 = ‘保衛(wèi)部’;

dept_name|emp_name|sex|salary|

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

保衛(wèi)部?? |??????? |?? |????? |

由于“保衛(wèi)部”沒有員工，我們需要使用外連接返回部門的信息；WHERE條件用于過濾 dept_id = 6 的數(shù)據(jù)；此時，員工表中返回的都是 NULL。

對于以上語句，如果將WHERE子句中的過濾條件放到ON子句中，結(jié)果將會完全不同：

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 = ‘保衛(wèi)部’);

dept_name|emp_name|sex|salary|

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

行政管理部|??????? |?? |????? |

人力資源部|??????? |?? |????? |

財務(wù)部?? |??????? |?? |????? |

研發(fā)部?? |????? ??|?? |????? |

銷售部?? |??????? |?? |????? |

保衛(wèi)部?? |??????? |?? |????? |

左外連接返回了所有的部門信息，而且員工信息都為 NULL；顯然，這不是我們期望的結(jié)果。我們可以通過執(zhí)行計劃分析一下為什么會這樣：

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 = ‘保衛(wèi)部’);

-> 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 = ‘保衛(wèi)部’)? (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 方式執(zhí)行連接操作；對于 department 使用全表掃描的方式返回 6 行記錄；對于 employee 表采用索引（idx_emp_dept）查找，同時使用“d.dept_name = ‘保衛(wèi)部’”作為過濾條件，循環(huán) 6 次返回了 0 行記錄；最終返回了上面的結(jié)果。

作為對比，我們可以看看將過濾條件放到WHERE子句時的執(zhí)行計劃：

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 = ‘保衛(wèi)部’;

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

??? -> Filter: (d.dept_name = ‘保衛(wèi)部’)? (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 方式執(zhí)行連接操作；對于 department 通過掃描返回 1 行記錄（d.dept_name = ‘保衛(wèi)部’）；對于 employee 表采用索引（idx_emp_dept）查找，同時使用 dept_id=d.dept_id 作為過濾條件，循環(huán) 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? |

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

行政管理部|??????? |?? |??????? |

人力資源部|??????? |?? |??????? |

財務(wù)部??? |??????? |?? |??????? |

研發(fā)部??? |趙云??? |男 |15000.00|

銷售部? ??|??????? |?? |??????? |

保衛(wèi)部??? |??????? |?? |??????? |

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? |

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

研發(fā)部?? |趙云??? |男 |15000.00|

名列前茅個查詢語句返回了所有的部門信息，以及部門中名叫“趙云”的員工；第二個查詢實際上等價于內(nèi)連接查詢。

一般來說，對于左外連接查詢，左表的過濾應(yīng)該使用WHERE子句，右表的過濾應(yīng)該使用ON子句；右外連接查詢正好相反；全外連接的過濾條件使用ON子句。

延伸閱讀：

二、resultMap 知識點

resultMap 元素用來描述如何將結(jié)果集映射到 Java 對象，使用 resultMap 對列表展示所需的必要字段來進行自動映射，特別是當數(shù)據(jù)庫的字段名和實體類 POJO 中的屬性名不一致的情況下，比如角色名稱，字段名/列名 column 是 roleName，而 User 對象的屬性名則為 userRoleName ，此時就需要做映射。

resultMap 元素的屬性值和子節(jié)點

id 屬性：少數(shù)標識，此 id 值用于 select 元素 resultMap 屬性的引用。

type 屬性：表示該 resultMap 的映射結(jié)果類型。

result 子節(jié)點：用于標識一些簡單屬性，其中 column 屬性表示從數(shù)據(jù)庫中查詢的字段名或別名， property 屬性則表示查詢出來的字段對應(yīng)的值賦給實體對象的哪個屬性。

說明：MyBatis 中在對查詢進行 select 映射的時候，返回類型可以用 resultType 也可以用 resultMap ，resultType和 resultMap 有一定關(guān)聯(lián)和區(qū)別，應(yīng)用場景也不同。