「MySQL筆記」left join-on-and 與 left join-on-where 的區(qū)別

1. 摘要

關于這兩種寫法的重要知識點摘要如下：

left-join 時，即使有相同的查詢條件，二者的查詢結果集也不同，原因是優(yōu)先級導致的，on 的優(yōu)先級比 where 高
on-and 是進行韋恩運算連接生成臨時表時使用的條件
where 是全部連接完生成臨時表后，再根據(jù)條件過濾
on 優(yōu)先級比 where 高，因此，理論上 on-and 寫法執(zhí)行效率比 on-where 高，速度更快
inner-join 時，不管是對左表還是右表進行篩選，on-and 和 on-where 都會對生成的臨時表進行過濾

2. 這兩種寫法有什么區(qū)別

兩者放置相同條件，之所以可能會導致結果集不同，就是因為優(yōu)先級。on的優(yōu)先級是高于where的。

首先明確兩個概念：

left join 關鍵字會從左表 (tb_user) 那里返回所有的行，即使在右表 (tb_score) 中沒有匹配的行。
數(shù)據(jù)庫在通過連接兩張或多張表來返回記錄時，都會生成一張中間的臨時表，然后再將這張臨時表返回給用戶。

在left join下，兩者的區(qū)別：

on是在生成臨時表的時候使用的條件，不管on的條件是否起到作用，都會返回左表 (tb_user) 的行。
where則是在生成臨時表之后使用的條件，此時已經不管是否使用了left join了，只要條件不為真的行，全部過濾掉。

在多表查詢時，on 比 where 更早起作用。系統(tǒng)首先根據(jù)各個表之間的聯(lián)接條件，把多個表合成一個臨時表后，再由 where 進行過濾，然后再計算，計算完后再由 having 進行過濾。由此可見，要想過濾條件起到正確的作用，首先要明白這個條件應該在什么時候起作用，然后再決定放在那里。

對于 join 參與的表的關聯(lián)操作，如果需要不滿足連接條件的行也在我們的查詢范圍內的話，我們就必需把連接條件放在 on 后面，而不能放在 where 后面，如果我們把連接條件放在了 where 后面，那么所有的left , right 等這些操作將不起任何作用，對于這種情況，它的效果就完全等同于 inner 連接。對于那些不影響選擇行的條件，放在 on 或者 where 后面就可以。

記住：所有的連接條件都必需要放在 on 后面，不然前面的所有 left,right 關聯(lián)將作為擺設，而不起任何作用。

3. 實例演示

第一步：新建2張表并插入數(shù)據(jù)

新建2張表：用戶表（tb_user）、用戶得分表（tb_score）

「MySQL筆記」left join-on-and 與 left join-on-where 的區(qū)別

表 tb_user 和 tb_score 數(shù)據(jù)

第二步：執(zhí)行查詢語句

（1）執(zhí)行 left-join-on-and 寫法SQL

select u.name,u.age,s.score
from tb_user u
		 left join tb_score s on s.user_id=u.id and s.score<90
where u.age>20;

執(zhí)行結果：

（2）執(zhí)行 left-join-on-where 寫法SQL

select u.name,u.age,s.score
from tb_user u
		 left join tb_score s on s.user_id=u.id 
where u.age>20
and s.score<90;

執(zhí)行結果：

第一個sql的執(zhí)行流程：首先找到 s 表的 score 小于90 的記錄行（on s.user_id=u.id and s.score<90），然后找到 u 的數(shù)據(jù)（即使不符合 s 表的規(guī)則），生成臨時表返回用戶。

第二個sql的執(zhí)行流程：首先生成臨時表，然后執(zhí)行 where 過濾 on s.score<90 不為真的結果集，最后返回給用戶。

因為on會首先過濾掉不符合條件的行，然后才會進行其它運算，所以按理說on是最快的。

第三步：分析執(zhí)行過程及結果

根據(jù)執(zhí)行過程，從上述執(zhí)行結果可以看出：

這2個SQL語句的執(zhí)行結果完全不同，使用時需要注意
兩者放置相同條件，之所以可能會導致結果集不同，就是因為優(yōu)先級：on的優(yōu)先級是高于where的
寫法 left-join-on-and 在連表查詢過程中先根據(jù) on-and 條件過濾右表（即 tb_score 表），再執(zhí)行 join 操作生成臨時表，然后對臨時表執(zhí)行 where 條件，因此，on-and 寫法會先對右表同時做2個條件的過濾
寫法 left-join-on-where 在連表查詢過程中先根據(jù) on 條件過濾右表，再執(zhí)行 join 操作生成臨時表，然后對臨時表執(zhí)行 where 條件, 因此，on-where 寫法會先對右表做1個條件的過濾，然后對 join 后的結果再執(zhí)行1個條件的過濾
由于 on 優(yōu)先級比 where 更高，執(zhí)行時機會更早，因此，理論上來說 on-and 寫法比 on-where 寫法效率更高、執(zhí)行更快