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)
117 Comments. Leave new
Although the subquery of query 2 returns results, these results can not be used in a comparison by the very nature of their NULL values. You can make the second query work also, by substituting a zero length string for the NULLS returned (SELECT ” AS PickedByPersonID) – this converts the nulls to strings and the strings are comparable and then this query also returns 62,972 rows. Bottom line: When there is a need to compare NULLS, there is a need to convert the NULLS.
Second query returns all rows where id column is null and other query compares with that id column. When there is null in where clause comparison it is always considered false.
Hello Sir,
The reason it will not provide you the result set is because you are comparing it with NULL i.e. PickedByPersonID.Value = NULL. It will never match.
However, you can achieve the result set by making a small change. Setting ANSI_NULLS to OFF. It will make NULL=NULL possible and you’ll get the 62,972 rows records. (This method is not recommended).
Thanks,
Ashish Sharma
Null means unknown so you really can’t compare a NULL value. In the case of the second query you are comparing NOT IN to the value of NULL in the sub query.
any time you compare a condition “NOT IN” (in the case of the second query) to NULL you will always get back nothing (0).
In the second example your inner query would only contain “NULL” values that were in the PickedByPersonID column. Then, in you outer query are trying to select all values for the PickedByPersonID column …. which considering the inner query are all NULLS (and I’m assuming there should be “0” NULL values) … no values should be returned.
The second Query the where clause pulls no Records so the is nothing for the NOT IN to compare to
I actually ran into this problem recently. Well, a similar problem. The issue is that when comparing to NULL, the keyword IS and comparing equality (= and but not used in your example) will find no matches. That is NULL IN (NULL) gives no results as does NULL = NULL. This is because NULL is not really a single value like 1 or ‘true’, but is an unknown value.
The first query you are saying where VALUE is NOT IN (non-NULL VALUES). The second query you are saying where VALUE IN (NULL).
On a side note, this is a good interview question too. NULL comparisons may give you results you are not expecting. Like the following query:
DECLARE @var1 VARCHAR(5)=NULL
DECLARE @var2 VARCHAR(5)=NULL
SELECT ‘HELLO WORLD’
WHERE @var1 = @var2
will give no rows even though you assigned NULL to both variables!. And if you switch the = to a or != (NOT equal) you get the same result of no rows returned.
The sub-select of the first query returns a list of values for PickedByPersonID. This is then compared to the PickedByPersonId in the outer select returning a value of TRUE or FALSE for each item in the list. TheTRUES are returned in the result set. The sub-select in the second query returns a list of nothing but NULL. A NULL in the list evaluates to UNKNOWN as opposed to TRUE or FALSE so therefore no TRUEs and an empty result set is returned.
2nd query nothing is equal or not equal or less than or greater than NULL
Hi! I would point first that NULL is about Unknown values. Comparing with null is UNKNOWN, unless you use IS (or IS NOT) NULL. When you query by specifying “WHERE ColumnX IN (‘a’, ‘b’)” SQL Server and others will transform it in “WHERE (ColumnX = ‘a’ OR ColumnX = ‘b’)”. When you query by specifying “WHERE ColumnX NOT IN (‘a’, ‘b’)” SQL Server and others will transform it in “WHERE (ColumnX ‘a’ AND ColumnX ‘b’)”. To compare NULL values we have to use “WHERE Column IS/(IS NOT) NULL”, so the SET to compare shoudn’t expect return only NULL. This would be made possible by using “SET ANSI_NULLS OFF” before the needed statements or maybe using other logic column to compare leaving the same clause on ColumnX. In the case of SQL Server a better compreension on this is available in .
ID not in (bunch of records with ID = null) gives 0 records. Null never matches.
The difference is because is null or is not null is not equal to in and not in
Let me explain, “is null” doesn’t return the same results that “= null”, and in reality,” in ” is just like “= ”
the same way, “is not null” doesn’t return the same as ” null” , and “not in” is like ” ”
as the result , if you get this
SELECT *
FROM [WideWorldImporters].[Sales].[Orders]
WHERE PickedByPersonID null
and compare with
SELECT *
FROM [WideWorldImporters].[Sales].[Orders]
WHERE PickedByPersonID is not null
those are two different operations that will have different results.
If inner query have null value then in or not in does not give any record
This issue is related to ANSI_NULL setting.
In case outer query has some results 1, 2, 3 and inner query returns NULL then the comparison would be like 1NULL or 2NULL or 3NULL. So in this case result of 1NULL is unknown because NULL comparisons with IDs are not interpreted by when ANSI_NULL setting is ON. However you can get results by changing the setting to ANSI_NULL OFF.
In the 2nd NOT IN query, For each row in the outer query (o/p: all the records),it, scan the bottom inner query (o/p only null records) and tries to match . This match always return False.
———–
select 1 where 1 NULL — no output , we think 1 is not equal to null so it should return 1, but it doesn’t, it is unable to compare.
select case when 1 NULL then ‘t’ else ‘f’ end — f output
Effectively, the second query does this:
SELECT *
FROM [WideWorldImporters].[Sales].[Orders]
WHERE PickedByPersonID NOT IN (NULL)
GO
So there is no way this could ever return any records, because the subquery returns NULL and Orders.PickedByPersonID is obviously never NULL..
This is because a NULL value is an unknown value therefore does not match anything.
My guess would be that you cannot make a null comparison in this manner. Null does not equal null. You would need to use something like except to make this second query work properly or resolve null to something default and then compare.
Joins on columns that allow nulls is problematic.
Changing the inner select to the something like the following, will yield the same results as the first query
Select isnull(PickedByPersonID,9999)
From [WideWorldImporters].[Sales].[Orders]
where PickedByPersonID is null
I’ve seen this issue before when tried a query similar to the second one when using “IS NULL”, and I had to change it to “= NULL”. not sure why “IS” did not apply the needed logic but it worked.