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
Hi Pinal,
Hope you are well.
Firstly, the first query added above is the more logical way to get records with valid or not null ‘PickedByPersonID’.
(note- we have another simplest way to get the same resultset).
But when you move NOT keyword from subquery to the where condition given the fact that you are still selecting NULL values of ‘PickedByPersonID’ in the subquery, the where condition is not really able to compare ‘PickedByPersonID’ with that of null values from subquery and hence getting zero/ no records in the resultset.
Regards,
Kavita Sakure
Null can only be condtionionally refeferenced with IS NULL or IS NOT NULL. =, , IN will all give no results and the 2nd query evaluates to “FIELD IN (NULL)”
Wouldn’t it depend on whether or not Sales Orders are ever entered with a null PickedByPersonID. If it is never null, the the first query would find them, but the second would not since the inner select yields no results.
When this puzzle will get the result?
My Response had the Different sign removed from it, now that I saw
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 ”DIFFERENT null” , and “not in” is like ” ”
as the result , if you get this
SELECT *
FROM [WideWorldImporters].[Sales].[Orders]
WHERE PickedByPersonID DIFFERENT null
and compare with
SELECT *
FROM [WideWorldImporters].[Sales].[Orders]
WHERE PickedByPersonID is not null
those are two different operations that will have different results.
Isn’t this SQL example kind of like an “Anyone who is not here, raise your hand.” type scenario?
Null can not be compare with any value, because null means nothing. So in Subquery you are only selecting Null values and these null can’t be compare in outer query either is IN or NOT IN operator.
The Sub query might have bring all the PickedByPersonID which are already there in [WideWorldImporters].[Sales].[Orders], so outer query wouldn’t left with any left over PickedByPersonID.
The Subquery using IN / IS NOT NULL returns rows where the primary and subquery PickedByPersonIDs are = to one another. The Subquery using NOT IN / IS NULL evaluates rows from the primary query where PickedByPersonID PickedByPersonID (from the subquery). The expression (NOT IN) PickedByPersonID NULL is UNKNOWN and therefore FALSE – returning 0 rows. The behavior of this evaluation can be changed by setting the ANSI_NULLS parameter. If you SET ANSI_NULLS ON, the 1 NULL expression is TRUE. Therefore the NOT IN / IS NULL query would reutrn the same number of rows as the IN / IS NOT NULL query.
Example:
IF OBJECT_ID(‘tempdb..#TEST_TBL’) IS NOT NULL
DROP TABLE #TEST_TBL;
CREATE TABLE #TEST_TBL
(
Col1 INT
,Col2 INT
);
INSERT INTO #TEST_TBL
SELECT
Col1
,Col2
FROM
(
VALUES
(
1, 1
)
,(
2, 2
)
) v ( Col1, Col2 );
/* IN / IS NOT NULL ; returns 2 rows */
SELECT
Col1
FROM
#TBL
WHERE
Col1 IN
(
SELECT Col1 FROM #TBL WHERE Col1 IS NOT NULL
);
/* NOT IN / IS NULL ; returns 0 rows */
SELECT
Col1
FROM
#TBL
WHERE
Col1 NOT IN
(
SELECT Col1 FROM #TBL WHERE Col1 IS NULL
);
SET ANSI_NULLS OFF;
/* IN / IS NOT NULL ; still returns 2 rows */
SELECT
Col1
FROM
#TBL
WHERE
Col1 IN
(
SELECT Col1 FROM #TBL WHERE Col1 IS NOT NULL
);
/* NOT IN / IS NULL ; returns 2 rows because with ANSI_NULLS = OFF */
SELECT
Col1
FROM
#TBL
WHERE
Col1 NOT IN
(
SELECT Col1 FROM #TBL WHERE Col1 IS NULL
);
SET ANSI_NULLS ON;
Sub query in second statement will have records with only null value for column PickedByPersonID, hence in outer query with condition NOT IN result wont come. NOT IN will applicable only if it has values
Reason is NULL you not able to say PickedByPersonID NOT IN (NULL)
From second query – Is null returns value contain only NULL , null can’t be compared to any value .
So out query won’t be able to validate NOT IN .
Hello, I belive this has to be with the fact that when you use IS NULL in the sub-query when you try to do “PickedByPersonID NOT IN” it’s like you where doing PickedByPersonID NULL that is an invalid operartion and allways return false.
It should use IS NULL or NOT IS NULL, that’s why the first one work good.
Thanks for this chalening subject to talk about comparing to a NULL value :D
Regards from Uruguay, South America
Felipe Schneider
NULL means any Value , there fore in the second query putting NOT in outside the inner table will give you 0 rows as any value from the outer query is equal to NULL and putting NOT in front is never true.
Cool. The seconde query is comparing with a NULL value. Anything compared with NULL will be evaluated as False. Therefor the seconde query does’t return any record.
Some exceptions to this rule are IS, EXEPT, INTERSECT
Because NULL is not a value and comparing some value/NULL with it always yields a FALSE.
In the 2nd query PickedByPersonID either can be an integer value or NULL and the sub query is going to return a set of NULLs. So the main query WHERE clause will always return a FALSE.
The second query amounts to WHERE PickedByPerson NOT IN (NULL) – which returns FALSE hence 0 result set.