關於join on和where執行順序分析

join on和where執行順序

1、join中相比where優先推薦on

WHERE子句中使用的連線語句，在資料庫語言中，被稱為隱性連線。INNER JOIN……ON子句產生的連線稱為顯性連線。（其他JOIN引數也是顯性連線）WHERE和INNER JOIN產生的連線關係，沒有本質區別，結果也一樣。但是！隱性連線隨著資料庫語言的規範和發展，已經逐漸被淘汰，比較新的資料庫語言基本上已經拋棄了隱性連線，全部採用顯性連線了。

2. 邏輯上一個query的執行順序（不是實際） 

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 
說是“邏輯上” 順序，因為實際執行時還要看索引，資料分佈等，看最終優化器如何處理，最真實的順序肯定是執行計劃展示的順序。

SQL語句中join連表時on和where後都可以跟條件，那麼對查詢結果集，執行順序，效率是如何呢？ 通過查詢資料發現： 
區別： 
on是對中間結果進行篩選，where是對最終結果篩選。 

執行順序： 
先進行on的過濾, 而後才進行join。 

效率： 
如果是inner join, 放on和放where產生的結果一樣, 但沒說哪個效率速度更高? 如果有outer join (left or right), 就有區別了, 因為on生效在先, 已經提前過濾了一部分資料, 而where生效在後. 
最後來了解下T-SQL對查詢邏輯處理。 
T-SQL邏輯查詢的各個階段(編號代表順序)： 
(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在查詢各個階級分別幹了什麼： 
（1）FROM 階段 
    FROM階段標識出查詢的來源表，並處理表運運算元。在涉及到聯接運算的查詢中（各種join），主要有以下幾個步驟： 
　　a.求笛卡爾積。不論是什麼型別的聯接運算，首先都是執行交叉連線（cross join），求笛卡兒積，生成虛擬表VT1-J1。 

      b.ON篩選器。這個階段對上個步驟生成的VT1-J1進行篩選，根據ON子句中出現的謂詞進行篩選，讓謂詞取值為true的行通過了考驗，插入到VT1-J2。 

     c.新增外部行。如果指定了outer join，還需要將VT1-J2中沒有找到匹配的行，作為外部行新增到VT1-J2中，生成VT1-J3。 

    經過以上步驟，FROM階段就完成了。概括地講，FROM階段就是進行預處理的，根據提供的運運算元對語句中提到的各個表進行處理（除了join，還有apply，pivot，unpivot） 

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

（3）GROUP BY階段 
      GROUP階段按照指定的列名列表，將VT2中的行進行分組，生成VT3。最後每個分組只有一行。 

（4）HAVING階段 
      該階段根據HAVING子句中出現的謂詞對VT3的分組進行篩選，並將符合條件的組插入到VT4中。 

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

（6）ORDER BY階段 
     根據ORDER BY子句中指定的列明列表，對VT5-3中的行，進行排序，生成遊標VC6.

如果是inner join, 放on和放where產生的結果一樣, 執行計劃也是一樣，但推薦使用on。但如果有outer join (left or right), 就有區別了, 因為on生效在先, 已經提前過濾了一部分資料, 而where生效在後，而且on對於outer join有不生效的情況,需要看and條件是作用在左表還是右表。

擴充套件：SQL執行順序join在where前面

• 取a表和b表join的資料sql

原本意圖是取a表的昨日分割區資料和b表的昨日分割區資料進行join，但是發現加上and b.dt = '${daily}'的條件後就取不到在a表中存在的資料了

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 = '浙江港都電子有限公司';

執行結果

• 原因是where執行是在join之後，join出來的臨時表中，由於在b表中沒有匹配到資料，所以b.dt為null，這時候執行where b.dt = '${daily}'的條件篩選不到這條資料。
• 優化方法

現在子查詢中將b表的昨日分割區查詢出來，再和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;

執行結果

可以看到正常查詢出a表中的資料了

到此這篇關於join on和where執行順序的文章就介紹到這了,更多相關join on和where執行順序內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！