????????????數(shù)據(jù)分析寫SQL取數(shù)回歸到本質(zhì)就是篩選或者過濾馍惹，對原始數(shù)據(jù)進(jìn)行某種給定條件的篩選或者過濾躺率，仔細(xì)思考一下表連接（join)不就是以另一張表為過濾條件、where后接的不就是過濾條件万矾、group by分組不就是把分組情況作為條件悼吱？說到底就是按照條件的先后順序一道一道把需要的數(shù)據(jù)篩選出來，因此條件過濾是SQL的基本技能良狈，理解透了如何用join后添、如何用where、如何用groupby這三大篩選方式的原理薪丁，基本SQL就不在話下遇西。

????????下面用實際案例數(shù)據(jù)還原真實取數(shù)場景，來從條件否定的角度幫助你在實戰(zhàn)中理解如何實現(xiàn)SQL條件過濾取數(shù)的過程严嗜，總結(jié)思路和規(guī)律粱檀。

需求：寫一條 SQL 語句找出在FactInternetSalesReason表中而不在DimSalesReason表中的SalesReasonkey和SalesReasonName，你該怎么做漫玄？

背景：數(shù)據(jù)來源于微軟示例數(shù)據(jù)庫茄蚯，一家銷售自行車制造公司的銷售數(shù)據(jù)，公司為了改善產(chǎn)品睦优，增進(jìn)用戶體驗渗常，需要了解顧客購買產(chǎn)品的消費理由，現(xiàn)在公司收集到兩張表汗盘，一張F(tuán)actInternetSalesReason表皱碘，包含訂單號salesordernumber和SalesReasonkey在內(nèi)三個字段，共計64515條記錄衡未，另一張表DimSalesReason記錄每個銷售因素尸执，包含SalesReasonkey和SalesReasonName在內(nèi)四個字段。因FactInternetSalesReason數(shù)據(jù)量大缓醋，現(xiàn)在先分組聚合后展示接下來將要操作的兩張表的數(shù)據(jù)如失，讓大家更清楚理解過程和結(jié)果：

分析：首先明確要取的數(shù)據(jù)字段是什么，需求中說了是FactInternetSalesReason表中的SalesReasonkey這一個字段送粱，不過需要篩選褪贵，篩選出不在DimSalesReason表中的記錄，這是典型的條件篩選，用where語句not in關(guān)鍵字篩選就可以了脆丁，還涉及到表連接（join）和分組（group by)世舰。需求很明確，也很簡單槽卫。但是我仍然提出五種思路供大家參考和動手實踐跟压，目的在于學(xué)會判斷哪些思路寫出的SQL是好的，這是SQL優(yōu)化的第一步歼培。

????????本篇是原創(chuàng)SQL解題總結(jié)系列第七篇震蒋，如果大家看了我之前的系列文章應(yīng)該都知道SQL題大都有四個角度去考慮解法，但是對這道題只用到了第一種思路躲庄，就是簡單查詢（復(fù)合查詢）查剖。后面四種方法沒有用到。為什么噪窘？因為在前面幾篇里排名問題笋庄、連續(xù)問題、累加問題是數(shù)據(jù)縱向之間發(fā)生關(guān)系倔监；兩個均值比較直砂、行轉(zhuǎn)列問題涉及縱向和橫向數(shù)據(jù)之間發(fā)生關(guān)系；但在簡單的條件過濾中數(shù)據(jù)縱向之間和橫向之間是沒有發(fā)生關(guān)系的浩习。再來看窗口函數(shù)哆键、自定義變量、自連接都解決的是數(shù)據(jù)縱向之間關(guān)系瘦锹，是在這里不適用的。這也給初學(xué)者提供寫SQL的思路闪盔，究竟什么時候用窗口函數(shù)或自連接弯院，什么時候不該用，是要判斷取的數(shù)據(jù)要不要在縱向或橫向發(fā)生關(guān)系泪掀。

????????簡單說一下解題思路听绳，主表是DimSalesReason，要取出的兩個字段也都在這張表中异赫，從表是FactInternetSalesReason椅挣，這里你就可以看到其實連接就是用從表篩選主表。那怎么篩選呢塔拳？下面從子查詢鼠证、連接查詢、聯(lián)合查詢?nèi)齻€角度來篩選靠抑，這也充分利用了所有SQL的基本知識量九，這也是我前面文章所說的一定理解基本SQL語句的功能，要實現(xiàn)某個目的可以創(chuàng)造條件讓這個功能實現(xiàn)。下面直接給出全部解法荠列，再一一對比解析类浪。

解法一 子查詢+not in

select SalesReasonkey,SalesReasonName from DimSalesReason where SalesReasonkey not in       (select SalesReasonkey        from (select SalesReasonkey,count(SalesReasonkey)              from FactInternetSalesReason              group by SalesReasonkey)a);

解法二 子查詢+not exists

select SalesReasonkey,SalesReasonName from DimSalesReason dwhere not exists       (select SalesReasonkey        from (select SalesReasonkey,count(SalesReasonkey)              from FactInternetSalesReason              group by SalesReasonkey)a       where d.SalesReasonkey = a.SalesReasonkey);

解法三 連接查詢+null

select d.SalesReasonkey,SalesReasonName from DimSalesReason d left join (select SalesReasonkey,count(SalesReasonkey) ???????????from?FactInternetSalesReason????????????group?by?SalesReasonkey)a           on d.SalesReasonkey = a.SalesReasonkeywhere a.SalesReasonkey is null;

解法四 連接查詢+isnull

select d.SalesReasonkey,SalesReasonName from DimSalesReason d left join (select SalesReasonkey,count(SalesReasonkey) ???????????from?FactInternetSalesReason????????????group?by?SalesReasonkey)a???????????on?d.SalesReasonkey?=?a.SalesReasonkeywhere isnull(a.SalesReasonkey);

解法五 聯(lián)合查詢

with t as (select SalesReasonkey,SalesReasonName ?from?DimSalesReason  union?select?SalesReasonkey,count(SalesReasonkey)??from?FactInternetSalesReason??group?by?SalesReasonkey)select SalesReasonkey,SalesReasonName from t group by SalesReasonkey having count(SalesReasonkey)=1;

????????上面這五種解法的執(zhí)行效率如下：

????????初學(xué)者應(yīng)該都知道SQL的書寫順序和執(zhí)行順序不一樣，下面這張圖分別顯示了是怎樣的順序：

????????可以看到SQL實際取數(shù)的過程是這樣的：

步驟1.最開始執(zhí)行的是from關(guān)鍵字肌似，先去訪問原數(shù)數(shù)據(jù)表费就，生成一張?zhí)摂M表t1（這張表包含原主表數(shù)據(jù)的全部數(shù)據(jù)，但是實際沒有取數(shù)）川队；

步驟2.如果有join關(guān)鍵字力细，就將join后面的表和t1表做個笛卡爾積，生成虛擬表t2（這張表包含原主表數(shù)據(jù)和從表的全部數(shù)據(jù)呼寸，但還是沒有取數(shù)）艳汽，如果沒有就跳過，直接執(zhí)行where以及之后語句对雪；

步驟3.上一步有join關(guān)鍵字河狐，則必須有on或者using關(guān)鍵字，否則語法上肯定報錯瑟捣。on的作用就相當(dāng)于where馋艺，是篩選器功能。它表示的是兩張表連接時在on后面條件下進(jìn)行的迈套，過濾掉不符合條件的生成虛擬表t3（依然沒有實際取數(shù)）捐祠，一般情況下是“=”連接條件，但是也可以接其他的桑李，很多初學(xué)者可能錯誤的以為on后面只能接等于號踱蛀；

步驟4.兩張表連接好后經(jīng)過on第一道篩選之后進(jìn)入到where篩選器，生成虛擬表t4,where是幾乎所有SQL必須要有的關(guān)鍵字贵白，因為它是最靠前的篩選器率拒，它可以為后面的執(zhí)行過程提高效率；

步驟5.在上一步篩選基礎(chǔ)上進(jìn)行g(shù)roup by分組篩選禁荒，生成虛擬表t5猬膨。group by分組本質(zhì)上就是where條件篩選再加上聚合，怎么理解呢呛伴？

上面可以理解為是一個先map(映射)再reduce(縮減)勃痴，在主流數(shù)據(jù)庫里使用group by 后必須使用聚合函數(shù)（reduce），因為要將每個組內(nèi)復(fù)合條件的數(shù)據(jù)縮減為一行就需要判斷或者計算热康，比如max/min/sum/count/avg等等沛申，然后再將結(jié)果一個一個union（聯(lián)合）起來；但是在MySQL里可以不用聚合姐军，會默認(rèn)取每組第一條數(shù)據(jù)污它，但是在sql server里是會報錯的。

步驟6.在步驟5的基礎(chǔ)上計算with cube或者rollup，這是對分組聚合的結(jié)果再匯總衫贬，就相當(dāng)于excel表中求和時右下角對每組求和結(jié)果再求和的那個值德澈。這里生成虛擬表t6。

步驟7.在步驟6基礎(chǔ)上固惯，對分組后的數(shù)據(jù)進(jìn)行第三道篩選梆造，它的作用也相當(dāng)于where，注意必須與groupby搭配使用葬毫。這里生成虛擬表t7镇辉。

步驟8.在步驟7篩選完畢之后，就開始執(zhí)行select語句實際取數(shù)贴捡，形成虛擬表t8忽肛，不過這里其實不是select第一次執(zhí)行，它在from之前已經(jīng)執(zhí)行過一次烂斋，但是它的作用不是取數(shù)屹逛，而是搭建好后面取的數(shù)據(jù)的表結(jié)構(gòu)和字段名，這也是為什么在having篩選條件里可以使用select語句中的字段別名汛骂。但是這僅限于MySQL,SqlServer罕模、oracle是嚴(yán)格遵循SQL標(biāo)準(zhǔn)的。

步驟9.在步驟8的基礎(chǔ)上對數(shù)據(jù)去重distinct帘瞭，生成虛擬表t9淑掌。

步驟10.在步驟8的基礎(chǔ)上對數(shù)據(jù)進(jìn)行排序，排序是比篩選成本更高的操作蝶念，where條件篩選其實也是依據(jù)某種執(zhí)行順序抛腕，但是它是隱性的，order by是顯性的媒殉。為了提高效率兽埃，我們往往需要在where和orderby后面的列增加索引，這樣查詢才會高效适袜。這就是為什么要索引的原因。這一步生成虛擬表t9舷夺。

步驟11.這里用limit關(guān)鍵字對上一步的表進(jìn)行截斷苦酱，取得最終結(jié)果。

????????理解了上面全部執(zhí)行順序再來理解上面五種解法就很簡單了给猾。我可以把篩選條件放在on后面（連接查詢）疫萤，也可以放在where后面（子查詢），也可以放在group by后面（聯(lián)合查詢）敢伸。在這個執(zhí)行順序過程中三道篩選扯饶，后一道都在前一道的基礎(chǔ)上，說明前面所處理的表數(shù)據(jù)是大于后面的，如果我們在越靠前的位置盡可能多的過濾掉數(shù)據(jù)尾序，那整體的執(zhí)行效率就會大大提高钓丰。這就是上面顯示為什么子查詢和連接查詢效率比聯(lián)合查詢高（理論上連接查詢比子查詢快，但因為數(shù)據(jù)量比較小每币，所以差不多携丁，反而還慢）。

????????最后還需要解釋的就是not in和not exists兰怠，null和isnull的用法和區(qū)別梦鉴。

????????如果百度查in和exists的區(qū)別的話，大都說不建議使用in揭保，但其實這需要因問題而定肥橙，要查看執(zhí)行計劃才能確定。因為涉及到全表掃描秸侣，數(shù)據(jù)內(nèi)容不一樣效率也會不一樣存筏。下面是區(qū)別：

1、對于not exists查詢塔次，內(nèi)表存在空值對查詢結(jié)果沒有影響方篮；對于not in查詢，內(nèi)表存在空值將導(dǎo)致最終的查詢結(jié)果為空励负。

2藕溅、對于not exists查詢，外表存在空值继榆，存在空值的那條記錄最終會輸出巾表；對于not in查詢，外表存在空值略吨，存在空值的那條記錄最終將被過濾集币，其他數(shù)據(jù)不受影響。

??????? null和isnull的區(qū)別也很簡單翠忠，前者沒有參數(shù)鞠苟，只是作為條件判斷，找出為空的記錄秽之；而后者是有參數(shù)的当娱，它會對第一個參數(shù)判斷，如果為空就null,不為空就不返回考榨。所以它的執(zhí)行效率會比is null慢一些跨细。

????????上面通過一個簡單的例子詳細(xì)的解釋了SQL的書寫順序和執(zhí)行順序，以加強讀者對SQL語句原理的理解河质，既可以幫助大家搞懂怎么寫SQL冀惭，也可以為SQL優(yōu)化提供思路震叙。

????????最后歡迎大家關(guān)注我，我是拾陸散休，搜索公眾號“二八Data”媒楼，更多技術(shù)干貨持續(xù)奉獻(xiàn)。