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.

SQL SERVER - Solution Part 2 - A Quick Puzzle on SQL JOIN and NULL - SQL Brain Teaser puzzle-500x281

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

Solarwinds

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?

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
SQL SERVER – Solution – A Quick Puzzle on JOIN and NULL – SQL Brain Teaser
Next Post
SQL SERVER – A Puzzle – Illusion – Confusion – April Fools’ Day

Related Posts

4 Comments. Leave new

  • Hi pinal,

    How are you doing..?

    I feel happy to say that this “JOINS and NULL” issue came up with earlier blog comments you know that is your first reply to me i have commented on some posts before but i did not get any reply for that, i was happy when i found response for this post

    https://blog.sqlauthority.com/2012/02/09/sql-server-inner-join-returning-more-records-than-exists-in-table/#comments

    That time i was bit confusing when i was seaing the answer for query 1. suddenly i thought why jeff facing the duplicate problem with Joins even that join query1 not showing all including NULL’s,

    i was serously thinking for some time i did some work later i realised with “NULL is NULL” and ‘not NULL=NULL’..

    However in this post you are right i have thought it earlier in “SQL SERVER – Solution – A Quick Puzzle on JOIN and NULL – SQL Brain Teaser” but you mentioned clearly that not changing the T-SQL qury, if you ok with chaning the Query we have another solution for getting NULL valuse from both the tables.
    (without using joins).

    SELECT * FROM dbo.FirstTable
    WHERE dbo.FirstTable.Col1 IS NULL

    UNION ALL

    SELECT * FROM dbo.SecondTable
    WHERE dbo.SecondTable.Col1 IS NULL

    Reply
  • Hi Pinal
    I initially wrong this but later today I run both queries and there is difference in execution plan as well, can you please explain two quries. Thanks,

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

    Khurram

    Reply
  • How we can call a store procedure in crystal report

    Reply
  • Hi Pinal Sir,
    Thank you for selecting this as one of the answer for puzzle.

    Thank$
    Chirag Satasiya

    Reply

Leave a Reply

Menu