Yesterday, we had an interesting discussion on the SQLAuthority page about a brain teaser related to SQL Server JOIN and NULL. The puzzle sparked a lot of conversation and it was fascinating to see how different readers approached the problem.

The puzzle consisted of three parts: setup, fill in the blanks with T-SQL code, and the expected query result. Many readers found the puzzle to be easy, but only a few provided the correct answer. It was a great learning experience for everyone involved.

Observations from the Puzzle

One interesting observation from this puzzle was how people interpreted the instructions. It was expected that readers would only add T-SQL code where there was a commented section, without modifying any other part of the code. However, many readers overlooked this detail and made changes to the query that was producing the result.

This puzzle highlighted the importance of paying attention to details and reading instructions carefully, especially for DBAs and developers. It’s crucial to understand the requirements and constraints before making any modifications.

Alternative Solutions

Some readers suggested alternative solutions by dropping the unique constraint on the tables. They argued that this would allow them to insert another NULL value and produce the desired result. However, the unique constraint was intentionally included to challenge readers’ understanding of how NULL works and how JOIN operates with NULL values.

While there were alternative queries that could produce the desired result, the puzzle was specifically designed to change the data set without modifying the provided query. This exercise aimed to enhance understanding of SQL Server concepts rather than finding workarounds.

The Correct Answer

The correct answer, provided by Samarth, was to insert a value of 1 into the FirstTable. This simple solution demonstrated the importance of carefully analyzing the puzzle and identifying the most appropriate action to achieve the desired outcome.

Just like this brain teaser, our SQL Server Questions and Answers course online offers many other learning nuggets. It covers a wide range of topics and provides comprehensive solutions to common SQL Server challenges.

Complete Solution

USE TempDB

-- Sample Table
CREATE TABLE FirstTable (
    Col1 INT
)

CREATE TABLE SecondTable (
    Col1 INT
)

ALTER TABLE FirstTable ADD CONSTRAINT UX_FirstTable UNIQUE NONCLUSTERED (Col1);
ALTER TABLE SecondTable ADD CONSTRAINT UX_SecondTable UNIQUE NONCLUSTERED (Col1);

INSERT INTO FirstTable (Col1) VALUES (NULL)

-- Solution of the puzzle - START -
INSERT INTO FirstTable (Col1) VALUES (1)
-- Solution of the puzzle - END -

INSERT INTO SecondTable (Col1) VALUES (NULL)

SELECT st.col1
FROM FirstTable ft
LEFT JOIN SecondTable st ON st.Col1 = ft.Col1

DROP TABLE FirstTable;
DROP TABLE SecondTable;

By understanding the concepts of JOIN and NULL in SQL Server, you can become a more proficient developer and DBA. Remember to pay attention to details, read instructions carefully, and think critically when faced with puzzles or challenges.

Stay tuned for more SQL Server articles and brain teasers!