Alternatives

ALTERNATIVES OF NOT IN

Most importantly, NOT EXISTS and LEFT JOIN / IS NULL are almost equal, while NOT IN is not.
These differ in handling of NULL values in the RIGHT side TABLE.

Some Alternatives of NOT IN

LOgic –

Example Code –

IF OBJECT_ID('Tempdb..#SampleTable1') IS NOT NULL
	DROP TABLE #SampleTable1;
IF OBJECT_ID('Tempdb..#SampleTable2') IS NOT NULL
	DROP TABLE #SampleTable2;

SET NOCOUNT ON;

CREATE TABLE #SampleTable1
( NUM INT NOT NULL );
GO

INSERT INTO #SampleTable1 (NUM)
VALUES (1),(2),(3),(4),(5) ;
GO

CREATE TABLE #SampleTable2 
( NUM INT NOT NULL );
GO

INSERT INTO #SampleTable2 (NUM)
VALUES (4),(5),(6),(7),(8) ;
GO

SELECT NUM AS [#SampleTable1] FROM #SampleTable1;
GO
SELECT NUM AS [#SampleTable2] FROM #SampleTable2;
GO
Sample Input

1. NOT EXISTS

--METHOD 1 (Using NOT EXISTS)
SELECT S1.NUM AS [NOT EXISTS] 
FROM #SampleTable1 S1 
WHERE NOT EXISTS (SELECT NUM FROM #SampleTable2 S2 WHERE S2.NUM=S1.NUM);
GO
NOT EXISTS

2. EXCEPT

--METHOD 2 (Using EXCEPT)
SELECT NUM AS [EXCEPT] 
FROM #SampleTable1 
EXCEPT 
SELECT NUM FROM #SampleTable2;
GO
EXCEPT

3. ANY

--METHOD 3 (Using = ANY)
SELECT S1.NUM AS [= ANY]
FROM #SampleTable1 S1
WHERE NOT (S1.NUM = ANY
(
SELECT S2.NUM
FROM #SampleTable2 S2
) );
ANY

4. OUTER APPLY

--METHOD 4 (Using OUTER APPLY and avoiding JOIN CONDITION)
SELECT S1.NUM AS [OUTER APPLY] 
FROM #SampleTable1 S1 
OUTER APPLY (
SELECT NUM FROM #SampleTable2 S2 WHERE S2.NUM=S1.NUM
) T 
WHERE T.NUM IS NULL;
GO
OUTER APPLY

5. LEFT JOIN / IS NULL

--METHOD 5 (Using LEFT JOIN/IS NULL)
SELECT S1.NUM AS [LEFT JOIN] 
FROM #SampleTable1 S1 
LEFT JOIN #SampleTable2 S2 ON S1.NUM=S2.NUM
WHERE S2.NUM IS NULL;
GO
LEFT JOIN / IS NULL

6. CORRELATED SUBQUERY

--METHOD 6 (Using CORRELATED SUBQUERY)
SELECT NUM AS [CORRELATED SUBQUERY]
FROM #SampleTable1 AS S1 
WHERE (SELECT COUNT(*) FROM #SampleTable2 S2
WHERE S2.NUM = S1.NUM) = 0;
GO

CORRELATED SUBQUERY

7. ALL

--METHOD 7 (Using <> ALL)
SELECT NUM AS [<> ALL]
FROM #SampleTable1
WHERE NUM <>ALL
(
SELECT NUM
FROM #SampleTable2
);
GO
ALL

8. CROSS APPLY

--METHOD 8 (CROSS APPLY)
SELECT T1.NUM AS [CROSS APPLY] 
FROM #SampleTable1 AS S1
CROSS APPLY
(
	SELECT S1.NUM
	EXCEPT
	SELECT NUM
	FROM #SampleTable2
) T1;
GO
CROSS APPLY

9. FULL OUTER JOIN

--METHOD 9 (Using FULL OUTER JOIN/IS NULL)
SELECT S1.NUM AS [FULL OUTER JOIN] 
FROM #SampleTable1 S1 
FULL OUTER JOIN #SampleTable2 S2 ON S1.NUM=S2.NUM
WHERE S2.NUM IS NULL;
GO
FULL OUTER JOIN / IS NULL

NOT IN

--METHOD 10 (Using NOT IN)
SELECT NUM AS [NOT IN] 
FROM #SampleTable1 
WHERE NUM NOT IN 
(SELECT NUM FROM #SampleTable2);
GO
NOT IN

Conclusion –

For Small Number of Data, NOT EXISTS is best choice irrespective of With/Without Index,Null or Non-Null Values. In this case, LEFT JOIN/IS NULL is least efficient while comparing to the performance of other Alternatives because of its behavior to not skipping the
already matched values with the right table and returning all the results and filtering them out at final steps using IS NULL filter.
But For Large set of Data, Sub query method is not recommended.

Thanks for reading!

Advertisement

One thought on “ALTERNATIVES OF NOT IN

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s