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

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

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

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

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

????????本篇是原創(chuàng)SQL解題總結(jié)系列第七篇，如果大家看了我之前的系列文章應(yīng)該都知道SQL題大都有四個(gè)角度去考慮解法，但是對這道題只用到了第一種思路，就是簡單查詢（復(fù)合查詢）。后面四種方法沒有用到。為什么？因?yàn)樵谇懊鎺灼锱琶麊栴}、連續(xù)問題、累加問題是數(shù)據(jù)縱向之間發(fā)生關(guān)系；兩個(gè)均值比較、行轉(zhuǎn)列問題涉及縱向和橫向數(shù)據(jù)之間發(fā)生關(guān)系；但在簡單的條件過濾中數(shù)據(jù)縱向之間和橫向之間是沒有發(fā)生關(guān)系的。再來看窗口函數(shù)、自定義變量、自連接都解決的是數(shù)據(jù)縱向之間關(guān)系，是在這里不適用的。這也給初學(xué)者提供寫SQL的思路，究竟什么時(shí)候用窗口函數(shù)或自連接，什么時(shí)候不該用，是要判斷取的數(shù)據(jù)要不要在縱向或橫向發(fā)生關(guān)系。

????????簡單說一下解題思路，主表是DimSalesReason，要取出的兩個(gè)字段也都在這張表中，從表是FactInternetSalesReason，這里你就可以看到其實(shí)連接就是用從表篩選主表。那怎么篩選呢？下面從子查詢、連接查詢、聯(lián)合查詢?nèi)齻€(gè)角度來篩選，這也充分利用了所有SQL的基本知識，這也是我前面文章所說的一定理解基本SQL語句的功能，要實(shí)現(xiàn)某個(gè)目的可以創(chuàng)造條件讓這個(gè)功能實(shí)現(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í)際取數(shù)的過程是這樣的：

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

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

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

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

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

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

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

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

步驟8.在步驟7篩選完畢之后，就開始執(zhí)行select語句實(shí)際取數(shù)，形成虛擬表t8，不過這里其實(shí)不是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條件篩選其實(shí)也是依據(jù)某種執(zhí)行順序，但是它是隱性的，order by是顯性的。為了提高效率，我們往往需要在where和orderby后面的列增加索引，這樣查詢才會高效。這就是為什么要索引的原因。這一步生成虛擬表t9。

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

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

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

????????如果百度查in和exists的區(qū)別的話，大都說不建議使用in，但其實(shí)這需要因問題而定，要查看執(zhí)行計(jì)劃才能確定。因?yàn)樯婕暗饺頀呙?，?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ù)的，它會對第一個(gè)參數(shù)判斷，如果為空就null,不為空就不返回。所以它的執(zhí)行效率會比is null慢一些。

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

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