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 🙂

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/