前言
本節(jié)我們來分析LEFT JOIN和NOT EXISTS，簡(jiǎn)短的內(nèi)容，深入的理解，Always to review the basics。
LEFT JOIN...IS NULL和NOT EXISTS分析
之前我們已經(jīng)分析過IN查詢?cè)谔幚砜罩禃r(shí)是基于三值邏輯，只要子查詢中存在空值此時(shí)則沒有任何數(shù)據(jù)返回，而LEFT JOIN和NOT EXISTS無論子查詢中有無空值上處理都是一樣的，當(dāng)然比較重要的是利用LEFT JOIN...IS NULL來檢查NULL?；诙叻祷氐慕Y(jié)果集是一樣的，下面我們開始直接用前面節(jié)所創(chuàng)建表來進(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如下
