Tags

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


SQL OUTPUT PUZZLE | The Joins – 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.

DATA.

Table1 – FirstTab

ID
1
2
NULL

Table2 – SecondTab

ID
1
3
NULL

Script – DDL and INSERT Sample Data

--

CREATE TABLE FirstTab
(
	 ID INT	
)
GO

INSERT INTO FirstTab VALUES
(1),
(2),
(NULL)
GO

SELECT * FROM FirstTab
GO

CREATE TABLE SecondTab
(
	ID INT	
)
GO

INSERT INTO SecondTab VALUES
(1),
(3),
(NULL)
GO

SELECT * FROM SecondTab
GO


--

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

--

SELECT COUNT(1) Cnt FROM FirstTab a
INNER JOIN SecondTab b ON a.Id  b.ID
--Note  : INNER JOIN with not equal to join

--

SOLUTION

--


So here the output will be 3 as 1  2 , 32 and 31
Cnt
-----------
3

(1 row affected)

Note - NULLs will not participate here in JOIN.

--

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

--

SELECT COUNT(1) Cnt FROM FirstTab a
LEFT JOIN SecondTab b ON a.Id  b.ID
--Note  : LEFT JOIN with not equal to join

--

SOLUTION

--

The output will be 4 here.

Cnt
-----------
4

(1 row affected)

Note - 3 rows for the Inner join and 1 NULL remaining from the left table.

--

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

--

SELECT COUNT(1) Cnt FROM FirstTab a
RIGHT JOIN SecondTab b ON a.Id  b.ID
--Note  : RIGHT JOIN with not equal to join

--

SOLUTION

--


The output will be 4 here.

Cnt
-----------
4

(1 row affected)

Note - 3 rows for the Inner join and 1 NULL remaining from the right table.


--

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

--

SELECT COUNT(1) Cnt FROM FirstTab a
FULL OUTER JOIN SecondTab b ON a.Id  b.ID
--Note  : FULL OUTER JOIN with not equal to join


--

SOLUTION

--


Cnt
-----------
5

The output will be 5. 3 for the inner join + 1 NULL for the left join and 1 for the right join.

--

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

--

SELECT COUNT(1) Cnt FROM FirstTab a
CROSS JOIN SecondTab b 
WHERE a.Id  b.ID

--

SOLUTION

--

This is similar as INNER JOIN. So the output will be 3.

--

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