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

ID VALUE
1 10
2 37
3 24
4 21
5 NULL
6 13

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 )

Id Value
3 24
4 21

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 )

Id Value
1 10
2 37
5 NULL
6 13

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”.
 ``` -- 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 -- ```