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.

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.

• Which is Faster – INSERT INTO SELECT or SELECT INTO? – Interview Question of the Week #187
• SQL SERVER – Puzzle – Why Such a Complicated Execution Plan for a Single Row, Single Table Query?
• SQL Puzzle – Schema and Table Creation – Answer Without Running Code

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