Tags

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


SQL OUTPUT PUZZLE | The IN & NOT IN Puzzle – How well do you know them ? | SQL Interview Question

In this puzzle you have to go through all the SQL queries and provide us the output with really executing them at the SSMS.

Script – DDL and INSERT Sample Data

--

CREATE TABLE FirstTab
(
	 ID INT
	,[Name] VARCHAR(10)
)
GO

INSERT INTO FirstTab VALUES
(5,'Pawan'),
(6,'Sharlee'),
(7,'Krish'),
(NULL,'Avtaar')
GO

SELECT * FROM FirstTab
GO

CREATE TABLE SecondTab
(
	ID INT	
)
GO

INSERT INTO SecondTab VALUES
(5),
(NULL)
GO

SELECT * FROM SecondTab
GO

--

DATA.

Table1 – FirstTab

ID Name
5 Pawan
6 Sharlee
7 Krish
NULL Avtaar

Table2 – SecondTab

ID
5
NULL

Q1. What will be the OUTPUT of the following statement?

--

SELECT COUNT(*) 
FROM FirstTab a WHERE a.Id NOT IN ( SELECT Id FROM SecondTab WHERE Id IS NULL )

--

SOLUTION

--


The output will be 0 here. 
If you have NULL inside the sub query and 
you are using NOT IN you will not get any rows.


--

Q2. What will be the OUTPUT of the following statement?

--

SELECT COUNT(*) 
FROM FirstTab a WHERE a.Id NOT IN ( SELECT Id FROM SecondTab WHERE Id = 5 )

--

SOLUTION

--

Here you will get 2 as the output. 
We are fetch records other than 2. 
So there are only 5 records - 6 and 7. 
NULL will not be considered here as it cannot be compared.

--

Q3. What will be the OUTPUT of the following statement?

--

SELECT COUNT(*) 
FROM FirstTab a WHERE a.Id NOT IN ( SELECT Id FROM SecondTab )

--

SOLUTION

--

The output will be 0 here. 
If you have NULL inside the sub query and 
you are using NOT IN you will not get any rows.

--

Q4. What will be the OUTPUT of the following statement?

--

SELECT COUNT(*) 
FROM FirstTab a WHERE a.Id NOT IN ( SELECT Id FROM SecondTab WHERE Id IS NOT NULL )


--

SOLUTION

--

Here you will get 2 as the output. 
We are fetch records other than 5 as 
NULL has been eliminated using IS NOT NULL condition. 
So there are only 2 records - 6 and 7. 
NULL will not be considered here as it cannot be compared.

--

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