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

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:

  1. Setup
  2. Fill in the blanks – T-SQL code
  3. Query and Resultset
When query was ran, it was giving following result:

The challenge was to write an additional code and produce the following result:

Here is an interesting observation in running this puzzle.

Comments Ignored

This puzzle was extremely simple, but the real complex part was how people comprehend the instruction. It was expected that they do not change any other part of the code, but add T-SQL where there was a commented code. Most of the readers, however, DID NOT read the comments properly. They modified the query which was producing the result. The learning is that as a DBA and Developer, one must pay attention to the details and read every instruction.

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 - http://wp.me/p2NUQ-4uJ
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 (http://blog.sqlauthority.com)

About these ads

6 thoughts on “SQL SERVER – Solution – A Quick Puzzle on JOIN and NULL – SQL Brain Teaser

  1. 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.

  2. Pingback: SQL SERVER – Solution Part 2 – A Quick Puzzle on JOIN and NULL – SQL Brain Teaser « SQL Server Journey with SQL Authority

  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #016 « SQL Server Journey with SQL Authority

  4. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s