join on和where執(zhí)行順序

1、join中相比where優(yōu)先推薦on

WHERE子句中使用的連接語(yǔ)句，在數(shù)據(jù)庫(kù)語(yǔ)言中，被稱為隱性連接。INNER JOIN……ON子句產(chǎn)生的連接稱為顯性連接。（其他JOIN參數(shù)也是顯性連接）WHERE和INNER JOIN產(chǎn)生的連接關(guān)系，沒(méi)有本質(zhì)區(qū)別，結(jié)果也一樣。但是！隱性連接隨著數(shù)據(jù)庫(kù)語(yǔ)言的規(guī)范和發(fā)展，已經(jīng)逐漸被淘汰，比較新的數(shù)據(jù)庫(kù)語(yǔ)言基本上已經(jīng)拋棄了隱性連接，全部采用顯性連接了。

2. 邏輯上一個(gè)query的執(zhí)行順序（不是實(shí)際） 

1. FROM 
2. ON 
3. JOIN 
4. WHERE 
5. GROUP BY 
6. WITH CUBE or WITH ROLLUP 
7. HAVING 
8. SELECT 
9. DISTINCT 
10. ORDER BY 
11. TOP 
說(shuō)是“邏輯上” 順序，因?yàn)閷?shí)際執(zhí)行時(shí)還要看索引，數(shù)據(jù)分布等，看最終優(yōu)化器如何處理，最真實(shí)的順序肯定是執(zhí)行計(jì)劃展示的順序。

SQL語(yǔ)句中join連表時(shí)on和where后都可以跟條件，那么對(duì)查詢結(jié)果集，執(zhí)行順序，效率是如何呢？ 通過(guò)查詢資料發(fā)現(xiàn)： 
區(qū)別： 
on是對(duì)中間結(jié)果進(jìn)行篩選，where是對(duì)最終結(jié)果篩選。 

執(zhí)行順序： 
先進(jìn)行on的過(guò)濾, 而后才進(jìn)行join。 

效率： 
如果是inner join, 放on和放where產(chǎn)生的結(jié)果一樣, 但沒(méi)說(shuō)哪個(gè)效率速度更高? 如果有outer join (left or right), 就有區(qū)別了, 因?yàn)閛n生效在先, 已經(jīng)提前過(guò)濾了一部分?jǐn)?shù)據(jù), 而where生效在后. 
最后來(lái)了解下T-SQL對(duì)查詢邏輯處理。 
T-SQL邏輯查詢的各個(gè)階段(編號(hào)代表順序)： 
(5)SELECT DISTINCT TOP(<top_specification>) <select_list>                      
(1)FROM <left_table> <join_type> JOIN <right_table> ON <on_predicate> 
(2)WHERE <where_predicate> 
(3)GROUP BY <group_by_specification> 
(4)HAVING <having_predicate> 
(6)ORDER BY <order_by_list> 

T-SQL在查詢各個(gè)階級(jí)分別干了什么： 
（1）FROM 階段 
    FROM階段標(biāo)識(shí)出查詢的來(lái)源表，并處理表運(yùn)算符。在涉及到聯(lián)接運(yùn)算的查詢中（各種join），主要有以下幾個(gè)步驟： 
　　a.求笛卡爾積。不論是什么類型的聯(lián)接運(yùn)算，首先都是執(zhí)行交叉連接（cross join），求笛卡兒積，生成虛擬表VT1-J1。 

      b.ON篩選器。這個(gè)階段對(duì)上個(gè)步驟生成的VT1-J1進(jìn)行篩選，根據(jù)ON子句中出現(xiàn)的謂詞進(jìn)行篩選，讓謂詞取值為true的行通過(guò)了考驗(yàn)，插入到VT1-J2。 

     c.添加外部行。如果指定了outer join，還需要將VT1-J2中沒(méi)有找到匹配的行，作為外部行添加到VT1-J2中，生成VT1-J3。 

    經(jīng)過(guò)以上步驟，F(xiàn)ROM階段就完成了。概括地講，F(xiàn)ROM階段就是進(jìn)行預(yù)處理的，根據(jù)提供的運(yùn)算符對(duì)語(yǔ)句中提到的各個(gè)表進(jìn)行處理（除了join，還有apply，pivot，unpivot） 

（2）WHERE階段 
     WHERE階段是根據(jù)<where_predicate>中條件對(duì)VT1中的行進(jìn)行篩選，讓條件成立的行才會(huì)插入到VT2中。 

（3）GROUP BY階段 
      GROUP階段按照指定的列名列表，將VT2中的行進(jìn)行分組，生成VT3。最后每個(gè)分組只有一行。 

（4）HAVING階段 
      該階段根據(jù)HAVING子句中出現(xiàn)的謂詞對(duì)VT3的分組進(jìn)行篩選，并將符合條件的組插入到VT4中。 

（5）SELECT階段 
　　這個(gè)階段是投影的過(guò)程，處理SELECT子句提到的元素，產(chǎn)生VT5。這個(gè)步驟一般按下列順序進(jìn)行 
        a.計(jì)算SELECT列表中的表達(dá)式，生成VT5-1。 
        b.若有DISTINCT，則刪除VT5-1中的重復(fù)行，生成VT5-2 
        c.若有TOP，則根據(jù)ORDER BY子句定義的邏輯順序，從VT5-2中選擇簽名指定數(shù)量或者百分比的行，生成VT5-3 

（6）ORDER BY階段 
     根據(jù)ORDER BY子句中指定的列明列表，對(duì)VT5-3中的行，進(jìn)行排序，生成游標(biāo)VC6.

如果是inner join, 放on和放where產(chǎn)生的結(jié)果一樣, 執(zhí)行計(jì)劃也是一樣，但推薦使用on。但如果有outer join (left or right), 就有區(qū)別了, 因?yàn)閛n生效在先, 已經(jīng)提前過(guò)濾了一部分?jǐn)?shù)據(jù), 而where生效在后，而且on對(duì)于outer join有不生效的情況,需要看and條件是作用在左表還是右表。

擴(kuò)展：SQL執(zhí)行順序join在where前面

• 取a表和b表join的數(shù)據(jù)sql

原本意圖是取a表的昨日分區(qū)數(shù)據(jù)和b表的昨日分區(qū)數(shù)據(jù)進(jìn)行join，但是發(fā)現(xiàn)加上and b.dt = '${daily}'的條件后就取不到在a表中存在的數(shù)據(jù)了

select substr(a.create_time,1,7) create_month,a.service_id,a.unit_number,a.company_name,coalesce(b.unified_social_credit_code,'unknown') unified_social_credit_code
,a.company_id,b.company_id as company_id_b,a.dt,b.dt
from clouduser.dz_company a 
left join clouduser.dz_company_detail b on a.company_id = b.company_id
where a.dt = '${daily}' 
--and b.dt = '${daily}'
and substr(a.create_time,1,7) = '2022-09'
and a.company_name = '浙江港都電子有限公司';

運(yùn)行結(jié)果

• 原因是where執(zhí)行是在join之后，join出來(lái)的臨時(shí)表中，由于在b表中沒(méi)有匹配到數(shù)據(jù)，所以b.dt為null，這時(shí)候執(zhí)行where b.dt = '${daily}'的條件篩選不到這條數(shù)據(jù)。
• 優(yōu)化方法

現(xiàn)在子查詢中將b表的昨日分區(qū)查詢出來(lái)，再和a表join

with
r1 as (select * from clouduser.dz_company_detail where dt = '2022-10-17'),
r2 as (
select substr(a.create_time,1,7) create_month,a.service_id,a.unit_number,a.company_name,coalesce(b.unified_social_credit_code,'unknown') unified_social_credit_code
,a.company_id,b.company_id as company_id_b
from clouduser.dz_company a 
left join r1 b on a.company_id = b.company_id
where a.dt = '${daily}' 
--and b.dt = '${daily}'
and substr(a.create_time,1,7) = '2022-09'
and a.company_name = '浙江港都電子有限公司'
)
select * from r2;

執(zhí)行結(jié)果

可以看到正常查詢出a表中的數(shù)據(jù)了

到此這篇關(guān)于join on和where執(zhí)行順序的文章就介紹到這了,更多相關(guān)join on和where執(zhí)行順序內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論