Use NOT IN operator carefully

by Amol 25. May 2014 20:50
Whenever you use NOT IN operator you will have to be more conscious. Following example will explain why we need to be careful while using NOT IN operator.
  
 
CREATE TABLE [dbo].[Languages]
(  
    Id INT IDENTITY(1,1),
    [Language] VARCHAR(256)
)
GO
CREATE TABLE [dbo].[Books]
(  
    ISBN INT,
    [Language] VARCHAR(256)
)
GO
INSERT INTO [dbo].[Languages]
SELECT 'English' UNION ALL
SELECT 'Hindi' UNION ALL
SELECT 'Spanish' UNION ALL
SELECT 'French' UNION ALL
SELECT 'German' 
GO
INSERT INTO [dbo].[Books]
SELECT 1,'English'
UNION ALL SELECT 2,'Spanish'
UNION ALL SELECT 3,NULL
GO
SELECT l.[Language]
FROM [dbo].[Languages] AS l
WHERE l.[Language] NOT IN (SELECT [Language] FROM [dbo].[Books]);
 
 
 
Language
----------------------------------------------------
(0 row(s) affected)
 
 
 

   

0 rows will be returned when you execute the above statements. And this not the correct answer. This is because ultimately NOT IN operator is equivalent to

"NOT (Language = English OR Language = Spanish OR Language = NULL)

If you need the correct answer you will have to convert the NULLs into blank.



SELECT l.[Language]
FROM [dbo].[Languages] AS l
WHERE l.[Language] NOT IN (SELECT ISNULL(l.[Language], '') FROM [dbo].[Languages])
OR

SELECT l.[Language]

FROM [dbo].[Languages] AS l
WHERE l.[Language] NOT IN (SELECT ISNULL([Language], '') FROM [dbo].[Books])
OR
  
SELECT l.[Language]
FROM [dbo].[Languages] AS l
WHERE l.[Language] NOT IN (SELECT [Language] FROM [dbo].[Books] WHERE [Language] IS NOT NULL)
OR

 

SELECT l.[Language]

FROM [dbo].[Languages] AS l
WHERE NOT EXISTS (SELECT * FROM [dbo].[Books] WHERE [Language] = l.[Language])

Tags:

General

Add comment




  Country flag
biuquote
  • Comment
  • Preview
Loading


Category

Recent Posts

Tag cloud