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

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 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:

  1. Setup
  2. Fill in the blanks – T-SQL code
  3. 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:

Expected Resultset:

Try harder. You will figure it out. If you cannot figure out and you want to give up, here is the answer.

If you do not feel like doing SQL any further, try the following image of Mars taken by Viking 1 in 1976.

Try to find the human face:

Do let me know if you like this kind of puzzle and we will have many more of  these.

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

About these ads

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

  1. Hi Pinal sir,

    We like this type puzzle. Its like game and fun.

    Here we have the expected resultset :

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

    UNION ALL

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

    Explanation :
    First we can’t use INNER JOIN, it provides blank resultset.
    So we have to go with other joins.

    By running first query we are getting result from FirstTable.
    We also want multiple NULL values from that.
    We have to take out NULL from Second Table by RIGHT OUTER JOIN.

    Regard$
    Chirag Satasiya

    Like

  2. Hello Pinal Sir,
    How r u ?

    Samarth reply is correct.

    INSERT INTO FirstTable (Col1)
    VALUES (4)

    we cant insert any value in the FirstTable to crate a second row. Left Outer join will return all rows from the Left table(Two rows in this case).

    Reason For this output : we are getting right table colum( col1 ) from this query. It will return NULL for both rows because when we compare NULL to any Value(Including NULL itself), The result will always be UNKNOWN (NULL).

    Thanks,
    Ashish Jain

    Like

    • “when we compare NULL to any Value(Including NULL itself), The result will always be UNKNOWN (NULL).” Technically, that isn’t true. First, a comparison must always return true or false so the assesment of the comparison can be made. Second, the assesment of NULL=NULL will return true if “SET ANSI_NULLS OFF” is executed. If it is set on, (default) the result is false. Third, no matter what the setting is, the assesment of NULL=NULL will return true if you try to insert a second null field value into a uniquely constrained field. That was why the second insert in the first table wasn’t a null value. And finally, fourth, you could truncate the second table and there still would be two null rows returned because the left join requires returning all the rows of the first table (unless a where clause overrides it)
      For strange results try the following query setting the first statement off and on:
      SET ANSI_NULLS ON
      DECLARE @tbl TABLE(i1 int UNIQUE)
      INSERT INTO @tbl VALUES (NULL),(2),(3),(4),(5)
      SELECT a.i1 first
      FROM @tbl a
      WHERE a.i1 NOT IN (SELECT i1 FROM @tbl WHERE i1 IS NULL OR i1 = 3)
      SELECT a.i1 second
      FROM @tbl a
      WHERE a.i1 NOT IN (SELECT i1 FROM @tbl WHERE i1 = 2)

      Like

  3. R Jagtap
    Your comment is awaiting moderation.
    I agree with Samarth , we have to insert any value in the first table as null=null condition is like

    Like

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

  5. Hi Pinal,

    I think we’ll have to use below code into ‘Missing T-SQL Statement’ section.

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

    Like

  6. Hey sir you must design a course SSEPD(Sql server expertise with pinal dave)..i will make sure i m first to enroll.

    About puzzle it was nice please keep up updating with this tits n bits of sql.it makes us confident…

    Like

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

  8. SELECT st.col1
    FROM FirstTable ft
    Right outer JOIN
    (
    select Col1 from SecondTable
    union all
    select NULL As Col1
    ) As st
    ON st.Col1 = ft.Col1

    Like

  9. Hi Pinal,

    Can you please help us understand whats the difference between Full Join and Cartisian product, because in above puzzel, when there are null values in the both tables i get two records in the result of Full join query (which is what is answer to your Puzzel) but if i have any other number or value rather then null lets say 1, i am getting only 1 record as result of the same full join query.

    Regards,
    Qutub…

    Like

  10. Hi Pinal,

    I have a different question on Joins, could you please state the reason why we have case in-sensitive joins on SQL Server?
    I have worked on different databases as well and we have case sensitive joins there. Is there a specific reason for this kind of behavior?

    Following SQL returns the data irrespective of the join conditions.

    SELECT * FROM TABLE1 A
    INNER JOIN TABLE2 B
    ON UPPER(A.NAME)=LOWER(B.NAME);

    Thanks in advance!!

    Regards,
    Litesh

    Like

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

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