Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

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 Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ 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/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com

Advertisements