SQL Puzzle – IN and IS NOT NULL – Strange Results

It has been a while since I posted a puzzle on this blog and lots of people have been sending me an email requesting to have a brain teaser. Today, let us see a very simple puzzle where we are getting strange results when using NOT IN and IS NULL in a T-SQL query.

Here look at two queries if you think about it both look pretty much the same. Both the queries are run for sample database WideWorldImporters. You can get this sample database downloaded from here.

Query 1: IN and IS NOT NULL

Run the following query for the sample database WideWorldImporters.

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

The query above will return 62,972 rows.

Query 2: NOT IN and IS NULL

Run the following query for the sample database WideWorldImporters.

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

The query above will return 0 rows.

Why Strange Results?

Now the question to you is why just moving NOT from a subquery to outer query is making such a huge difference in the result. If you think about it, logically both the queries are the same. If you put any number for outer query or inner subquery, the result will come the same. However, there is one reason, why the result is different.

Here is the question one time – Why such a huge difference in the Resultset by moving NOT keyword?

Leave your answer in the comments section. All the answers will be kept unpublished for one week and after that, they will be all published. I will select three of the individuals randomly who has left a comment on this blog post and will send them free one month’s access to SQL Server Performance Tuning Practical Workshop – Recorded Classes.

If you have any such puzzle, please do send it to my way and I will be happy to publish with due credit to you.

Update (July 9, 2020): The winner of the contest is Mike Mohanty. 

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