July 12, 2009 at 5:38 am
Why does Not in returns an empty set when there's a Null value, whereas Not exists gives results.
July 12, 2009 at 7:57 am
Hello,
I don’t understand exactly your question but when ANSI_Nulls is On, then direct comparison of Null values do not return True.
May be look at the Books Online Topic “SET ANSI_NULLS (Transact-SQL)” and then try the following code with ANSI_Nulls turned On and then Off:-
Declare @MyTable Table (MyCol VarChar(50))
Insert @MyTable Values ('One')
Insert @MyTable Values ('Two')
Insert @MyTable Values (Null)
Set ANSI_Nulls On
-- Set ANSI_Nulls Off
Select
MyCol
From
@MyTable
Where
MyCol In ('One', 'Two', Null)
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
July 12, 2009 at 8:19 am
Thanks John for your response, I have the following Not exists Query which gives me a result:
SELECT custid, companyname
FROM Sales.Customers AS C
WHERE country = N'Spain'
AND NOT EXISTS
(SELECT * FROM Sales.Orders AS O
WHERE O.custid = C.custid)
whereas The Not in code below gives me an empty set:
SELECT custid, companyname
FROM Sales.Customers AS C
WHERE country = N'Spain'
AND custid NOT IN(SELECT custid FROM Sales.Orders);
The reason for this is that there is a Null row in the customers table.
My Question is: why does 'Not exists' query returns a row, but 'not in' query an empty set.
July 12, 2009 at 8:45 am
Because they are different queries. The logic of SQL's treatment of nulls is not very consistent unfortunately and that's one reason why many people try to avoid nulls altogether.
In the case of IN the rule is that IN returns FALSE only if the comparison is FALSE for every row in the subquery. That can't be the case if one of the values is null and therefore IN won't give you a FALSE result and NOT IN can't return TRUE if one of the values is a null.
July 12, 2009 at 12:39 pm
See http://weblogs.sqlteam.com/peterl/archive/2009/03/19/NULL-NOT-IN-conundrum.aspx
N 56°04'39.16"
E 12°55'05.25"
July 13, 2009 at 2:56 am
Thank you guys for the responses
I am still not clear why Not Exists doesn't give me the same result as 'Not in' which should be an empty result when there's a null value.
Peso,
I had a look at the link that you provided, but that again concentrates on how 'not in' behaves with Nulls.. I want to know how 'Not Exists' behaves as it gives me results even if there's a null value in the row.
July 13, 2009 at 3:06 am
There is a comment which deals with NULL and NOT EXISTS.
Read the comments in the link I posted earlier.
N 56°04'39.16"
E 12°55'05.25"
July 13, 2009 at 3:26 am
Well this is quite a simple thing sarvesh, whenever ANSI Null is encountered it is considered to be an active tuple while dealing with NOT EXISTS, because of which you get the result containing null.
But with NOT IN this is not the case and just like a dead cell such records are left out without being considered as an active one and therefore u dont get any result with such records...
July 19, 2009 at 9:26 pm
It for the same reason you cannot say ...
SELECT CASE WHEN NULL = NULL THEN 1 ELSE 0 END
... and ever expect to get a "1" unless you mess with the server ansi null settings... and you simply must not do that. 😉 In fact, they're going to remove that setting in future releases of SQL Server so you really need to read up on how NULL's work.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply