Performance difference between NOT Exists and LEFT JOIN IN SQL Server

Question

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.

Answer 1

Go for NOT EXISTS generally.

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

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.

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 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