SQL Server中NOT存在和LEFT JOIN之间的性能差异

I can have following query in order to check existing of record in target table.

我可以有以下查询,以检查目标表中的记录的现有。

Select * 
From Table1
Where Column1 not in (select Column2 from Table2)

Select *
from Table1
left join Table2 where Table1.Column1 = Table2.Column2
Where Table2.IdColumn is Null

I want to know which query has better performance.

我想知道哪个查询具有更好的性能。

1 个解决方案

#1

Go for NOT EXISTS generally.

一般去寻找NOT EXISTS。

It is more efficient than NOT IN if the columns on either side are nullable (and has the semantics you probably desire anyway)

如果任何一方的列都可以为空,那么它比NOT IN更有效(并且具有你可能想要的语义)

Left join ... Null sometimes does the whole join with a later filter to preserve the rows matching the is null and can be much less efficient.

左连接... Null有时会使用后面的过滤器进行整个连接以保留与null匹配的行,并且可能效率低得多。

An example demonstrating this is below. Notice the extra operators in the NOT IN plan and how the outer join plan blows up to create a join of over 1 million rows going into the filter.

以下是一个证明这一点的例子。注意NOT IN计划中的额外运算符以及外部联接计划如何爆炸以创建超过100万行的连接进入过滤器。

Not Exists

Outer Join ... NULL

Not In

CREATE TABLE Table1 (
     IdColumn INT IDENTITY PRIMARY KEY,
     Column1  INT NULL,
     Filler   CHAR(8000) NULL,
     UNIQUE(Column1, IdColumn) );

CREATE TABLE Table2 (
     IdColumn INT IDENTITY PRIMARY KEY,
     Column2  INT NULL,
     Filler   CHAR(8000) NULL,
     UNIQUE(Column2, IdColumn) );

INSERT INTO Table2 (Column2)
OUTPUT      INSERTED.Column2
INTO Table1(Column1)
SELECT number % 5
FROM   master..spt_values

SELECT *
FROM   Table1 t1
WHERE  NOT EXISTS (SELECT *
                   FROM   Table2 t2
                   WHERE  t2.Column2 = t1.Column1)

SELECT *
FROM   Table1
WHERE  Column1 NOT IN (SELECT Column2
                       FROM   Table2)

SELECT Table1.*
FROM   Table1
       LEFT JOIN Table2
         ON Table1.Column1 = Table2.Column2
WHERE  Table2.IdColumn IS NULL

DROP TABLE Table1, Table2