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

  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:

Solarwinds

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

Expected Resultset:

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

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:

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

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

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

Solarwinds
,
Previous Post
SQL SERVER – INNER JOIN Returning More Records than Exists in Table
Next Post
SQL SERVER – Solution – A Quick Puzzle on JOIN and NULL – SQL Brain Teaser

Related Posts

20 Comments. Leave new

  • INSERT INTO FirstTable (Col1)
    VALUES (1)

    Reply
  • 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

    Reply
  • you are providing simplest form of SQL. I am hoping much more from your side. You are master & teach us to become expert on SQL.

    Reply
  • 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

    Reply
  • INSERT INTO FirstTable (Col1)
    VALUES (”)

    Reply
    • “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)

      Reply
  • SELECT st.col1
    FROM FirstTable ft
    FULL JOIN SecondTable st ON st.Col1 = ft.Col1
    GO

    Reply
  • Pinal,
    Love the puzzle. Keep them comming. Love your Blog!

    Reply
  • I agree with Samarth , we have to insert any value in the first table as null=null condition is like “”

    Reply
  • 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

    Reply
  • 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

    Reply
  • 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…

    Reply
  • SELECT st.col1
    FROM FirstTable ft
    FULL OUTER JOIN SecondTable st ON st.Col1 = ft.Col1

    Result :

    col1
    ——
    NULL
    NULL

    Reply
  • 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

    Reply
  • SELECT st.col1
    FROM FirstTable ft
    right JOIN SecondTable st ON st.Col1 = ft.Col1
    GO

    Reply
  • 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…

    Reply
  • 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

    Reply
  • Interesting post to whom willing to learn sql.. Here below my ans!
    INSERT INTO FirstTable (Col1)
    VALUES (“any values here”)

    Reply
  • Sudhir Kumar Rai
    June 12, 2017 5:23 pm

    with CtrNull as
    (

    SELECT st.col1
    FROM FirstTable ft
    LEFT JOIN SecondTable st ON st.Col1 = ft.Col1
    ),
    CteNull as
    (
    SELECT st.col1
    FROM FirstTable ft
    LEFT JOIN SecondTable st ON st.Col1 = ft.Col1
    )

    SELECT * from CtrNull
    union all
    SELECT * from CteNull

    Reply

Leave a Reply

Menu