NOT IN 的替代写法

NOT IN 的替代写法

在SQL中我们有时需要实现类似以下的功能：

A表和B表经FCode关联，读出在A表中不存在于B表的FCode。

一般的写法是使用NOT IN，但NOT IN比较耗时，其实我们可以使用Left Join与Isnull函数结合起来写，这样速度更快。并且记录数越多，相差越大。

SET NOCOUNT ON

--建立测试环境

IF OBJECT_ID('tempdb..#t1') IS NOT NULL

  DROP TABLE tempdb..#t1

GO

 

IF OBJECT_ID('tempdb..#t2') IS NOT NULL

  DROP TABLE tempdb..#t2

GO

 

CREATE TABLE #t1 (

  ID INT ,

  FCode VARCHAR(10)

  )

 

 DECLARE @ROWS INT ,--插入记录数

         @TYPE VARCHAR(3),  --类型，即单据前缀

         @I   INT, --单据起始号

         @CI  VARCHAR(10)

 

SELECT @ROWS=500000,@TYPE='TEST',@I=1

 

WHILE @I<@ROWS+1

BEGIN

    SELECT @CI=LTRIM(RTRIM(CONVERT(VARCHAR,@I)))

    INSERT INTO#t1 VALUES (@i, @TYPE+REPLICATE('0',10-LEN(@TYPE)-LEN(@CI))+ @CI)

    SELECT @I=@I+1

    IF @I <@ROWS+1

      CONTINUE

    ELSE

      BREAK   

END

 

SELECT * INTO #T2 FROM #t1 WHERE ID % 3>0

 

----取出#T1中不存在于#T2中的FCode的记录

--使用NOT IN

SELECT * FROM #T1 WHERE FCode NOT IN (SELECT FCode FROM #T2)

 

--使用Left join 和ISNULL

SELECT a.* FROM #T1 aLEFT JOIN #T2 b

 ON a.FCode = b.FCode

 WHERE ISNULL(b.FCode,'')=''

 

SET NOCOUNT OFF