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

Download PowerPivot V1

by Amol 26. August 2013 20:32

Tags:

Downloads

T-SQL to get string between two strings

by Amol 17. July 2013 09:17

Below is the user defined function created in SQL Server to find the string between two strings.

 

CREATE FUNCTION dbo.udf_GetStringBetween2Strings 
(
	@String varchar(max), 
	@FirstString varchar(256), 
	@SecondString varchar(256)
)
RETURNS VARCHAR(max) 
AS
BEGIN

	DECLARE @FirstPosition int,  @SecondPosition int
	SET @FirstPosition = CHARINDEX(@FirstString,@String) + LEN(@FirstString)
	SET @SecondPosition = CHARINDEX(@SecondString,@String) 
	RETURN (SELECT SUBSTRING(@String, @FirstPosition, @SecondPosition - @FirstPosition))

END
GO

DECLARE @MyString varchar(256) = 'Bla bla bla xyxz Find my name Amol Rajmane in this string.'
SELECT dbo.udf_GetStringBetween2Strings (@MyString, 'Find my name ', ' in this') 
GO

 

 

Result is "Amol Rajmane"

Tags: ,

General

Category

Recent Posts

Tag cloud