QL 提供了多種對數據進行過濾的方式,包括WHERE、HAVING以及ON子句等。雖然它們都能夠實現類似的功能,但是你知道它們之間的區別嗎?讓我們一起來探討一下。
除非特殊說明,以下內容適用於各種數據庫,包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite 等;其中的示例表和數據來源於 GitHub。
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子句中只能使用分組字段或者聚合函數。
⚠️SQLite 雖然允許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中指定其他過濾條件。上面語句不但結果相同,數據庫的執行計劃也相同。以 MySQL 為例,以上語句的執行計劃如下:
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。
Oracle 支持在WHERE子句的右/左側使用 (+) 表示左/右外連接,但是無法表示全外連接。
對於以上語句,如果將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;顯然,這不是我們期望的結果。我們可以通過執行計劃分析一下為什麼會這樣,仍然以 MySQL 為例:
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子句。
閱讀更多 掌趣網絡 的文章