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

February 10, 2012

It seems that we all love to solve puzzles. On SQLAuthority Page, we have been playing the number game and those who are playing with us know how much fun we are having. Sometimes, the answers are so innovative and informative that they open up those aspects of the technology which I have not thought of. Today, I have a very relaxing puzzle and a SQL Brain Teaser for all of you. It is based on my earlier blog post on INNER JOIN and NULL, so I suggest reading the said post first if you want to get the complete idea.

The puzzle has three parts:

Setup
Fill in the blanks – T-SQL code
Query and Resultset

We will set up the sample table first. We will run the sample query, which will give us some result. You will have to figure out what the missing script will be, which in turn will generate a specific displayed result.

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
--------------------------------------
-- Missing T-SQL Statement
--------------------------------------
-- SQL Query
SELECT st.col1
FROM FirstTable ft
LEFT JOIN SecondTable st ON st.Col1 = ft.Col1
GO
-- Clean up
DROP TABLE FirstTable;
DROP TABLE SecondTable;
GO

Current Resultset:

SQL SERVER - A Quick Puzzle on JOIN and NULL - SQL Brain Teaser puzcurrent