SQL SERVER – Puzzle SET ANSI_NULLS and Resultset – SQL in Sixty Seconds #052

Earlier I have posted a puzzle where I was receiving different results when I executed two different queries. I encourage all of you to read the original puzzle here, the puzzle had received many fantastic responses and I have later blogged about the solution of the puzzle over here.

Now I have decided to extend the same puzzle and take it to the next level. In earlier puzzle I had value of the ANSI_NULLS was set to ON. Now in this puzzle let us set the value of the ANSI_NULLS to OFF. When the value of ANSI_NULLS was off at that time, the result of the both the queries is almost identical. The key reason behind this behavior is ANSI_NULLS setting.

However, the puzzle is to answer that why the different queries are producing almost same results when there is a different setting of ANSI_NULL.

Solarwinds

Let us see the same concept in following SQL in Sixty Seconds Video:

Here is the script used in this episode:

-- Original Puzzle
SQL SERVER – SQL Puzzle of SET ANSI_NULL – Win USD 50 worth Amazon Gift Cards and Bubble Copter R/C
-- Script SET ANSI_NULLS ON; -- Query1 SELECT 'SQLAuthority' AS Statement11 WHERE 'Authority' IN ('S','Q', 'L', 'Authority', NULL); -- Query 2 SELECT 'SQLAuthority' AS Statement12 WHERE 'Authority' NOT IN ('S','Q', 'L', NULL); -- Puzzle Solution
SQL SERVER- Solution – SQL Puzzle of SET ANSI_NULL
------------------------------------------------------ -- New puzzle SET ANSI_NULLS OFF; -- Query3 SELECT 'SQLAuthority' AS Statement11 WHERE 'Authority' IN ('S','Q', 'L', 'Authority', NULL); -- Query 4 SELECT 'SQLAuthority' AS Statement12 WHERE 'Authority' NOT IN ('S','Q', 'L', NULL);

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

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

Solarwinds
, , ,
Previous Post
SQL SERVER- Solution – SQL Puzzle of SET ANSI_NULL
Next Post
SQL SERVER – Add Identity Column to Table Based on Order of Another Column

Related Posts

1 Comment. Leave new

  • When you set ANSI_NULLS off, it will not follow the ISO Standard and in comparison with column and NULL, it will always return TRUE.

    Query 3 :
    ‘Authority’ = ‘S’ OR ‘Authority’ = ‘Q’ OR ‘Authority’ = ‘L’ OR ‘Authority’ = ‘Authority’ OR ‘Authority’ = NULL

    Returns FALSE OR FALSE OR FALSE OR TRUE OR TRUE so the final result is TRUE.
    RESULT : the query will return the value SQLAUTHORITY
    ===================================
    Query 4 :
    ‘Authority’ != ‘S’ AND ‘Authority’ != ’Q’ AND ‘Authority’ != ’L’ AND ‘Authority’ != NULL

    Returns TRUE AND TRUE AND TRUE AND TRUE so the final result is TRUE.

    RESULT : the query will return the value SQLAUTHORITY

    Reply

Leave a Reply

Menu