Implementation of NOT IN operator with out using NOT IN....

One question that people always ask is “Is there any other way to do it”. Even if you give all the possible solutions to a problem still the same question arises.

If some one is asking you a question, I have a problem with my SQL query using “NOT IN” operator. It is taking long time to run. Can you give me alternative solution to the “NOT IN”? This is very generic question and the best answer to this kind of question is "It Depends".

I am sure the question looks very familiar to most of us. In this post I am going to provide different ways to implement “NOT IN” operator. Although it’s going to be extremely difficult to say which one is better? It completely depends on the situation and need.

Using NOT IN Operator
==================
SELECT ProductID,
Name,
ProductNumber,
SellStartDate
FROM Production.Product
WHERE ProductID NOT IN (1, 3, 4, 10)

Using Logical Operators
===================
SELECT ProductID,
Name,
ProductNumber,
SellStartDate
FROM Production.Product
WHERE ProductID != 1 AND ProductID != 3
AND ProductID != 4 AND ProductID != 10

Using NOT EXISTS Operator with UNION ALL
==================================
SELECT p.ProductID,
p.Name,
p.ProductNumber,
p.SellStartDate
FROM Production.Product p
WHERE NOT EXISTS( SELECT * FROM (
SELECT 1 V UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 10 ) z
where z.v = p.ProductID )

Using Left Outer Join or Left Join
=========================

SELECT p.ProductID,
p.Name,
p.ProductNumber,
p.SellStartDate
FROM Production.Product p
left join
(
SELECT 1 V UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 10 ) z
on z.v = p.ProductID
WHERE z.v IS NULL