SQL Puzzle – Solution to Strange Results – IN and IS NOT NULL –

Earlier I had published a blog post about a puzzle SQL Puzzle – IN and IS NOT NULL – Strange Results. Today we will see a solution to strange results puzzle and also the name of the winner.

If you want you can refer my earlier blog post for the script which generates the puzzle.

Here is the image which I had used in the previous blog post.

SQL Puzzle - Solution to Strange Results - IN and IS NOT NULL - strangeresults-800x705

Now the question was Why such a huge difference in the Resultset by moving NOT keyword?

The answer to this is very simple because in SQL Server the NULL is evaluated very differently because of the ANSI NULL settings. Here is I am pasting the answer of contest Winner Mike Mohanty, who also wins free access to SQL Server Performance Tuning Practical Workshop – Recorded Classes.

The solution to Strange Results by Mike Mohanty

The issue is IS NOT NULL is very different from != NULL and that is why you are facing this situation.

Two NULL Values can be compared as well in SQL Server

For example, the following result will not return the same values

SELECT 1
WHERE NULL = NULL

If you want to change the behavior of how NULL works, you may want to change by writing the following set statement on the top of the query:

SET ANSI_NULLS OFF
SELECT 1
WHERE NULL = NULL

Now NULL will be compared properly. Try out your query too and you will find it also returns the same results.

SET ANSI_NULLS OFF
SELECT *
FROM [WideWorldImporters].[Sales].[Orders]
WHERE PickedByPersonID NOT IN (
    SELECT PickedByPersonID 
    FROM [WideWorldImporters].[Sales].[Orders]
    WHERE PickedByPersonID IS NULL)
GO

I hope you enjoyed this puzzle and participating in it. I will be publishing all the answers now and once again congratulations to Mike Mohanty for correct answers. There are many valid and correct answers to this puzzle and I strongly encourage you to visit the original blog post here.

Here are a few other interesting puzzles which you may find interesting.

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

, ,
Previous Post
SQL Puzzle – IN and IS NOT NULL – Strange Results
Next Post
Solve Puzzle about Data type – SQL in Sixty Seconds #108

Related Posts

2 Comments. Leave new

  • ‘SET ANSI_NULLS OFF’ is a good solution, but there needs to be a search for an alternative solution as the ability to disable ANSI NULLS is liable to be deprecated in future SQL Server versions : https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2016?view=sql-server-ver15

    Reply
  • My strange results using ANSI_NULLS OFF

    1) SET ANSI_NULLS OFF
    SELECT * FROM [dbo].[Table_1]
    WHERE Column_int NOT IN (SELECT NULL)
    result: rows > 0

    SET ANSI_NULLS OFF
    SELECT * FROM [dbo].[Table_1]
    WHERE Column_int NOT IN (SELECT NULL UNION ALL SELECT NULL)
    result: rows > 0

    SET ANSI_NULLS OFF
    SELECT * FROM [dbo].[Table_1]
    WHERE Column_varchar NOT IN (SELECT NULL UNION ALL SELECT NULL)
    result: all rows, include NULL (!)

    SET ANSI_NULLS OFF
    SELECT * FROM [dbo].[Table_1]
    WHERE Column_varchar NOT IN (SELECT NULL)
    result: rows = 0 (!)

    2) SET ANSI_NULLS OFF
    SELECT 1
    WHERE 1 NOT IN (SELECT NULL)
    result: 1

    SET ANSI_NULLS OFF
    SELECT 1
    WHERE ‘1’ NOT IN (SELECT NULL)
    result: error (!)
    Cannot continue the execution because the session is in the kill state.
    A severe error occurred on the current command. The results, if any, should be discarded.

    Why? ANSI_NULLS OFF does not support working with varchar type?

    Reply

Leave a Reply

Menu