Yesterday was really fun. I asked a simple Brain Teaser and we had excellent conversation on SQLAuthority Page as well SQL SERVER – A Quick Puzzle on JOIN and NULL – SQL Brain Teaser. That was an easy puzzle for those who have attended the SQL Server Questions and Answers online course.
Here is a quick recap of the puzzle. Lots of people said it is a very easy puzzle, but the correct answer was provided by only a few readers. There were lots of conversation on Facebook page and lots of emails I received, many saying that there was some sort of an error in the puzzle when there was really none. I am glad that lots of reader have attempted this simple-looking puzzle and learned something for sure.
The puzzle had three parts:
- Setup
- Fill in the blanks – T-SQL code
- Query and Resultset
The challenge was to write an additional code and produce the following result:
Here is an interesting observation in running this puzzle.
Comments Ignored
Changed the Final Query
Many changed the query which was producing the final result. Well, this was good practice for the brain but would not lead to the intended outcome. Here are a few queries which will produce the desired result. However, the puzzle was to change the data set and not to modify the query provided.
Dropping Unique Constraint
A few readers sent me email writing that I have problem in the schema of the tables. As they wanted to insert another NULL value in the table, they wanted to suggest that there should not be Unique Constraint on the tables. This way, they can insert another value in the table and produce the desired result. Honestly, the reason why I had put the Unique Constraint there was because I wanted people not to insert NULL value again and produce the result very quickly. It was important to understand how NULL works and how JOIN works with NULL.
Correct Answer
The answer was very easy and Samarth posted the correct answer a few moments after the question was posted.
INSERT INTO FirstTable (Col1) VALUES (1)
Just like this puzzle, there are many other learning nuggets in our SQL Server Questions and Answers course online.
Complete Solution Query
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
----------------------------------------------
-- Solution of the puzzle - START -
INSERT INTO FirstTable (Col1)
VALUES (1)
GO
-- Solution of the puzzle - END
----------------------------------------------
INSERT INTO SecondTable (Col1)
VALUES (NULL)
GO
--------------------------------------
SELECT st.col1
FROM FirstTable ft
LEFT JOIN SecondTable st ON st.Col1 = ft.Col1
GO
DROP TABLE FirstTable;
DROP TABLE SecondTable;
GO
Reference: Pinal Dave (https://blog.sqlauthority.com)
6 Comments. Leave new
Hi pinal,
it was a nice game with ‘JOINS vs NULL’.
Hi pinal,
Can you please send me the SQL Puzzle on my mail id. Its [email removed] . I am working as developer in one on the IT company and wants to improve my logic , I am tinging that your puzzle will be help full for me.
Hai sir,
I am a beginner in sql. I tried it. But used “UNION ALL”. This puzzle is very interesting and nice.
we need not insert additional records, puzzle is to deal with nulls and joins.
so, by Eliminating one table and another insert, we could produce 2 nulls with below query
SELECT st.col1
FROM FirstTable ft
full OUTER JOIN FirstTable st ON st.Col1 = ft.Col1
GO