by pavan bangad
22. March 2017 23:39
2fefc7fb-8630-4243-bdc8-1532eb6e5a4d|0|.0
Tags:
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])
by Amol
26. August 2013 20:32