Lets consider this scenario: (This example is taken from AdventureWorks) Find all customers who do not have Sales. It could be written in three ways:
SET ANSI_NULLS ON
-- Method 1: Using NOT IN
SELECT CustomerID, AccountNumber
FROM Sales.Customer
WHERE CustomerID NOT IN
(
SELECT CustomerID
FROM Sales.SalesOrderHeader
)
-- Method 2: Using NOT EXISTS
SELECT CustomerID, AccountNumber
FROM Sales.Customer C
WHERE NOT EXISTS
(
SELECT 1
FROM Sales.SalesOrderHeader S
WHERE S.CustomerID = C.CustomerID
)
-- MEthod 3: Using LEFT JOIN
SELECT c.CustomerID, c.AccountNumber
FROM Sales.Customer c
LEFT JOIN Sales.SalesOrderHeader soh
ON soh.CustomerID = c.CustomerID
WHERE soh.CustomerID IS NULL
All the above queries give the same results, show the same execution plan, take the same amount of resources in IO and executed almost at the same time. However, this example is quite simple. The column CustomerID is not having any null values. When it comes with null values, the results may vary:
