SQL Puzzle | The In NotIn Puzzle

Well this came to one of my friend as a new requirement where customer is passing some comma separated values (as a parameter) and he wants to search this data in a table using In clause and Not In clause. So my friend accepted one more parameter. This parameter will inform us whether we need to go for In Clause or Not In Clause.

So, In below case we have to find all data from the table where @Vals NOT present in the table.

DECLARE @Vals AS VARCHAR(100) = ‘21,24’
DECLARE @InOrNotIn AS BIT = 1 –( 1 means NOT IN and 0 means IN )

So, In below case we have to find all data from the table where @Vals present in the table.

DECLARE @Vals AS VARCHAR(100) = ‘21,24’
DECLARE @InOrNotIn AS BIT = 0 –( 1 means NOT IN and 0 means IN )

Please check out the sample input values and expected output below.

Sample Input

Expected Output

For In Output required is –

DECLARE @Vals AS VARCHAR(100) = ‘21,24’
DECLARE @InOrNotIn AS BIT = 0 –( 1 means NOT IN and 0 means IN )

For Not In Output required is –

DECLARE @Vals AS VARCHAR(100) = ‘21,24’
DECLARE @InOrNotIn AS BIT = 1 –( 1 means NOT IN and 0 means IN )

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE InNOTIn
(
	 ID INT
	,VALUE INT
)
GO

INSERT INTO InNOTIn
VALUES
(1,10),
(2,37),
(3,24),
(4,21),
(5,NULL),
(6,13)
GO

SELECT * FROM InNOTIn
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION 1 | Using Select and LEFT JOIN

--


DECLARE @Vals AS VARCHAR(100) = '21,24'
DECLARE @InOrNotIn AS BIT = 0 --( 1 means NOT IN and 0 means IN )

SELECT Id,Value FROM 
(
	SELECT I.* , CASE WHEN S.Value IS NULL THEN 1 ELSE 0 END flg
	FROM InNOTIn I LEFT JOIN dbo.udf_Split(@Vals,',') S ON s.VALUE = I.VALUE
)t WHERE t.flg = @InOrNotIn


--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com