SQL PUZZLE | The IN-NOTIN & UNION Puzzle | SQL Interview Question

Well It is one of the most interesting interview questions I have. Find all the rows where ID IS not in (‘Id9’, ‘Id10’, ‘Id11’) and If the ID is in (‘Id9’, ‘Id10’, ‘Id11’) with num columns value is greater than 0 the we also need to include that rows. One restriction is that we cannot use the UNION or UNION ALL in this question. 🙂 . You also need to find out rows where .For more details please see the sample input and expected output.

Sample Input

Num Vals Id Description
5 2 Id6 T1
5 2 Id7 T2
5 2 Id8 T3
1 2 Id9 T4
0 2 Id10 T5
0 2 Id11 T6

Expected Output

Num Vals Id Description
5 2 Id6 T1
5 2 Id7 T2
5 2 Id8 T3
1 2 Id9 T4

Script – DDL and INSERT Sample Data

 ```-- CREATE TABLE TheNOTIn ( Num INT ,Vals INT ,Id VARCHAR(10) ,[Description] VARCHAR(5) ) GO INSERT INTO TheNOTIn VALUES (5,2,'Id6','T1'), (5,2,'Id7','T2'), (5,2,'Id8','T3'), (1,2,'Id9','T4'), (0,2,'Id10','T5'), (0,2,'Id11','T6') GO SELECT * FROM TheNOTIn GO -- ```

SOLUTION – 1

 ```-- SELECT * FROM TheNOTIn WHERE 1 = CASE WHEN ID NOT IN ('Id9', 'Id10', 'Id11') THEN 1 WHEN ID IN ('Id9', 'Id10', 'Id11') AND Num > 0 THEN 1 ELSE 2 END -- ```

Output – 1

 ```-- Num Vals Id Description ----------- ----------- ---------- ----------- 5 2 Id6 T1 5 2 Id7 T2 5 2 Id8 T3 1 2 Id9 T4 (4 rows affected) -- ```

SOLUTION – 2 | With UNION, IN AND NOT IN

 ```-- SELECT * FROM TheNOTIn WHERE ID NOT IN ('Id9', 'Id10', 'Id11') UNION SELECT * FROM TheNOTIn WHERE ID IN ('Id9', 'Id10', 'Id11') and Num > 0 -- ```

Output – 2

 ```-- Num Vals Id Description ----------- ----------- ---------- ----------- 1 2 Id9 T4 5 2 Id6 T1 5 2 Id7 T2 5 2 Id8 T3 (4 rows affected) -- ```

SOLUTION – 3 | IIF

 ```-- SELECT * FROM TheNOTIn WHERE 1 = IIF(ID NOT IN ('Id9', 'Id10', 'Id11'),1,IIF( ID IN ('Id9', 'Id10', 'Id11') AND Num > 0,1,2)) -- ```

Output – 3

 ```-- Num Vals Id Description ----------- ----------- ---------- ----------- 5 2 Id6 T1 5 2 Id7 T2 5 2 Id8 T3 1 2 Id9 T4 (4 rows affected) -- ```

Enjoy 🙂

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/