Forgot your password?
Document Actions
Typical solutions to avoid using "NOT IN" on SQL Server
It's very common to use the operator NOT IN to retrive rows in table (or SQL statement) that are not in another table or other SQL Statement. This operator sometimes can slow down the performance and sometimes returns unexpected results. There are options to avoid it.
-- First Let's create some tables and populate them
CREATE TABLE T1 (ID INT)
CREATE TABLE T2 (ID INT)
INSERT INTO T1 VALUES (1)
INSERT INTO T1 VALUES (2)
INSERT INTO T1 VALUES (3)
INSERT INTO T2 VALUES (1)
INSERT INTO T2 VALUES (2)
-- To retrieve the rows in T1 but not in T2 We can use NOT IN (ID 3)
SELECT ID FROM T1
WHERE ID NOT IN (SELECT ID FROM T2)
-- Not In works, but as the number of records grows, NOT IN performs worse
-- We can use NOT EXISTS instead of
SELECT ID FROM T1
WHERE NOT EXISTS
(SELECT ID FROM T2 WHERE T1.ID = T2.ID)
-- Another option is to use LEFT OUTER JOIN
SELECT T1.ID FROM T1
LEFT OUTER JOIN T2 ON T1.ID = T2.ID
WHERE T2.ID IS NULL
-- In SQL Server 2005 or Later, We can use EXCEPT
-- Even if it is not have exactly the same behavior
SELECT ID FROM T1
EXCEPT
SELECT ID FROM T2
-- Not In not always return the expected results when null values are allowed
-- It won't work as expected
INSERT INTO T2 VALUES (NULL)
-- Because Null comparisons always return false, comparing null with other values doesn't return rows
SELECT ID FROM T1
WHERE ID NOT IN (SELECT ID FROM T2)
-- But the other options still works
SELECT ID FROM T1
WHERE NOT EXISTS
(SELECT ID FROM T2 WHERE T1.ID = T2.ID)
SELECT T1.ID FROM T1
LEFT OUTER JOIN T2 ON T1.ID = T2.ID
WHERE T2.ID IS NULL
SELECT ID FROM T1
EXCEPT
SELECT ID FROM T2
I suggest to avoid the use of NOT IN if you have a large number of records or the columns in comparison accepts NOT NULL.
Hacker Rangers - Gamification for CyberSecurity