前言
本節(jié)我們來(lái)分析LEFT JOIN和NOT EXISTS，簡(jiǎn)短的內(nèi)容，深入的理解，Always to review the basics。
LEFT JOIN...IS NULL和NOT EXISTS分析
之前我們已經(jīng)分析過(guò)IN查詢(xún)?cè)谔幚砜罩禃r(shí)是基于三值邏輯，只要子查詢(xún)中存在空值此時(shí)則沒(méi)有任何數(shù)據(jù)返回，而LEFT JOIN和NOT EXISTS無(wú)論子查詢(xún)中有無(wú)空值上處理都是一樣的，當(dāng)然比較重要的是利用LEFT JOIN...IS NULL來(lái)檢查NULL。基于二者返回的結(jié)果集是一樣的，下面我們開(kāi)始直接用前面節(jié)所創(chuàng)建表來(lái)進(jìn)行測(cè)試。在BigTable和SmallerTable上首先未創(chuàng)建索引
USE TSQL2012 GO DBCC FREEPROCCACHE DBCC DROPCLEANBUFFERS SET STATISTICS IO ON SET STATISTICS TIME ON SELECT BigTable.ID, SomeColumn FROM BigTable LEFT OUTER JOIN SmallerTable ON BigTable.SomeColumn = SmallerTable.LookupColumn WHERE LookupColumn IS NULL SELECT ID, SomeColumn FROM BigTable WHERE NOT EXISTS (SELECT LookupColumn FROM SmallerTable WHERE SmallerTable.LookupColumn = BigTable.SomeColumn)
二者執(zhí)行CPU Time和elapsed Time如下
