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

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

WHERE 與 HAVING

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

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

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

select dept_id, count(*)

from employee

where count(*) > 5

group by dept_id;

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

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

select dept_id, count(*)

from employee

group by dept_id

having count(*) > 5;

dept_id|count(*)|

——-|——–|

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

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

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

— 統(tǒng)計(jì)每個(gè)部門(mén)月薪大于等于 30000 的員工人數(shù)

select dept_id, count(*)

from employee

group by dept_id

having salary >= 30000;

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

從性能的角度來(lái)說(shuō)，HAVING子句中如果使用了分組字段作為過(guò)濾條件，應(yīng)該替換成WHERE子句；因?yàn)閃HERE可以在執(zhí)行分組操作和計(jì)算聚合函數(shù)之前過(guò)濾掉不需要的數(shù)據(jù)，性能會(huì)更好。下面示例中的語(yǔ)句 1 應(yīng)該替換成語(yǔ)句 2：

— 語(yǔ)句 1

select dept_id, count(*)

from employee

group by dept_id

having dept_id = 1;

— 語(yǔ)句 2

select dept_id, count(*)

from employee

where dept_id = 1

group by dept_id;

當(dāng)然，WHERE和HAVING可以組合在一起使用。例如：

select dept_id, count(*)

from employee

where salary > 10000

group by dept_id

having count(*) > 1;

dept_id|count(*)|

——-|——–|

????? 1|?????? 3|

該語(yǔ)句返回了月薪大于 10000 的員工人數(shù)大于 1 的部門(mén)；WHERE用于過(guò)濾月薪大于 10000 的員工；HAVING用于過(guò)濾員工數(shù)量大于 1 的部門(mén)。

WHERE 與 ON

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

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

— 語(yǔ)句 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|

— 語(yǔ)句 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|

— 語(yǔ)句 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|

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

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

盡管如此，仍然建議將兩個(gè)表的連接條件放在ON子句中，將其他過(guò)濾條件放在WHERE子句中；這樣語(yǔ)義更加明確，更容易閱讀和理解。對(duì)于上面的示例而言，推薦使用語(yǔ)句 3 的寫(xiě)法。

對(duì)于外連接而言，連接條件只能用ON子句表示，因?yàn)閃HERE子句無(wú)法表示外連接的語(yǔ)義。例如：

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)部”沒(méi)有員工，我們需要使用外連接返回部門(mén)的信息；WHERE條件用于過(guò)濾 dept_id = 6 的數(shù)據(jù)；此時(shí)，員工表中返回的都是 NULL。

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

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|

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

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

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

財(cái)務(wù)部?? |??????? |?? |????? |

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

銷(xiāo)售部?? |??????? |?? |????? |

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

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

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)

查詢(xún)計(jì)劃顯示使用 Nested loop left join 方式執(zhí)行連接操作；對(duì)于 department 使用全表掃描的方式返回 6 行記錄；對(duì)于 employee 表采用索引（idx_emp_dept）查找，同時(shí)使用“d.dept_name = ‘保衛(wèi)部’”作為過(guò)濾條件，循環(huán) 6 次返回了 0 行記錄；最終返回了上面的結(jié)果。

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

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)

查詢(xún)計(jì)劃顯示使用 Nested loop left join 方式執(zhí)行連接操作；對(duì)于 department 通過(guò)掃描返回 1 行記錄（d.dept_name = ‘保衛(wèi)部’）；對(duì)于 employee 表采用索引（idx_emp_dept）查找，同時(shí)使用 dept_id=d.dept_id 作為過(guò)濾條件，循環(huán) 1 次返回了 0 行記錄。

我們?cè)倏匆粋€(gè)外連接的示例：

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

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

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

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

財(cái)務(wù)部??? |??????? |?? |??????? |

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

銷(xiāo)售部? ??|??????? |?? |??????? |

保衛(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|

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

一般來(lái)說(shuō)，對(duì)于左外連接查詢(xún)，左表的過(guò)濾應(yīng)該使用WHERE子句，右表的過(guò)濾應(yīng)該使用ON子句；右外連接查詢(xún)正好相反；全外連接的過(guò)濾條件使用ON子句。

延伸閱讀：

二、resultMap 知識(shí)點(diǎn)

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

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

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

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

result 子節(jié)點(diǎn)：用于標(biāo)識(shí)一些簡(jiǎn)單屬性，其中 column 屬性表示從數(shù)據(jù)庫(kù)中查詢(xún)的字段名或別名， property 屬性則表示查詢(xún)出來(lái)的字段對(duì)應(yīng)的值賦給實(shí)體對(duì)象的哪個(gè)屬性。

說(shuō)明：MyBatis 中在對(duì)查詢(xún)進(jìn)行 select 映射的時(shí)候，返回類(lèi)型可以用 resultType 也可以用 resultMap ，resultType和 resultMap 有一定關(guān)聯(lián)和區(qū)別，應(yīng)用場(chǎng)景也不同。