SQL SERVER – Solution Part 2 – A Quick Puzzle on SQL JOIN and NULL – SQL Brain Teaser

Some questions are timeless and they never grow old; no matter how much they grow old their interest never dies. Earlier, I asked a simple puzzle based on a conversation on SQLAuthority Page, and have received an overwhelming response from readers. I still get emails related to this puzzle every day. Let us see a quick puzzle between SQL Join and SQL Null.

Before we move on, I suggest you read the question and its initial solution here:

Question: A Quick Puzzle on JOIN and NULL – SQL Brain Teaser

Solution: Solution – A Quick Puzzle on JOIN and NULL – SQL Brain Teaser

The puzzle had three parts and the users had to fill up the second part, which I had left it for them to complete. I received many responses, and here is another response which is absolutely valid and worthy of acknowledgment. Once again, before you read the solution, I suggest you give the puzzle a quick try. Perhaps you’d find another neat way to solve this puzzle.

The puzzle had three parts:

1. Setup
2. Fill in the blanks – T-SQL code
3. Query and Resultset

Just like this puzzle, there are many other learning nuggets in our SQL Server Questions and Answers course online.

Complete Solution Query – SQL Join

USE TempDB
GO
-- Sample Table
CREATE TABLE FirstTable (Col1 INT)
CREATE TABLE SecondTable (Col1 INT)
GO
ALTER TABLE FirstTable ADD CONSTRAINT
UX_FirstTable UNIQUE NONCLUSTERED (Col1);
ALTER TABLE SecondTable ADD CONSTRAINT
UX_SecondTable UNIQUE NONCLUSTERED (Col1);
GO
INSERT INTO FirstTable (Col1)
VALUES (NULL)
GO
INSERT INTO SecondTable (Col1)
VALUES (NULL)
GO
--------------------------------------
-- Solution
SELECT st.col1
FROM FirstTable ft
RIGHT JOIN SecondTable st ON st.Col1 = ft.Col1
UNION ALL
SELECT st.col1
FROM FirstTable ft
LEFT JOIN SecondTable st ON st.Col1 = ft.Col1
GO
DROP TABLE FirstTable;
DROP TABLE SecondTable;
GO

Now the reason I did not select this as a valid answer is because if you notice original solution required to insert one row into the FirstTable. However, when you look at carefully, you can notice even this solution is a valid solution. There were many other solutions which require to change T-SQL which I initially provided, however, as per the puzzle that was not allowed.

Any other solution out there?

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.