Inequality comparisons against null question

This seems very very strange. We thougth it was only an issue with
uniqueidentifiers, but we have now determined that any inequality
comparison between a value and a null does not result in true or false.
The row with that comparison just disappears!
Here is a scrip that demos this:
Declare @.tmp table (
a int,
b int
)
Insert @.tmp Values (1, 1)
Insert @.tmp Values (2, 3)
Insert @.tmp Values (4, null)
Select * From @.tmp
-- Returns what I expected
Select * From @.tmp Where a = b
-- Returns what I expected
Select * From @.tmp Where a <> b
-- Hmmm... does not return the row (4,null)
Select * From @.tmp Where !(a = b)
-- This does not return the row (4,null) either
This seems to be the case with any inequality comparisons agains NULL.
Very very strange. I had assumed all along that:
1 != NULL
would be True.
I could be dumb, but the documentation in SQL did not explain this very
well. The issues is only with inequality it seems, and doing the
ANSI_NULLS does not change the issue. So lifes goes... I guess.Comparisons against NULL evaluates to the logical state UNKNOWN. But it was deemed impractical for
the DBMS to raise its virtual hands in the air and say "I don't know" (throw an error) as soon as a
query has a comparison against NULL. So, for a WHERE clause, UNKNOWN will be FALSE. That is the same
for <> as well as =. An interesting point is that for a GROUP BY and a constraint, UNKNOWN will be
TRUE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Daniel Santa Cruz" <dstcruz@.gmail.com> wrote in message
news:1136492061.330974.56460@.o13g2000cwo.googlegroups.com...
> This seems very very strange. We thougth it was only an issue with
> uniqueidentifiers, but we have now determined that any inequality
> comparison between a value and a null does not result in true or false.
> The row with that comparison just disappears!
> Here is a scrip that demos this:
> Declare @.tmp table (
> a int,
> b int
> )
> Insert @.tmp Values (1, 1)
> Insert @.tmp Values (2, 3)
> Insert @.tmp Values (4, null)
> Select * From @.tmp
> -- Returns what I expected
> Select * From @.tmp Where a = b
> -- Returns what I expected
> Select * From @.tmp Where a <> b
> -- Hmmm... does not return the row (4,null)
> Select * From @.tmp Where !(a = b)
> -- This does not return the row (4,null) either
> This seems to be the case with any inequality comparisons agains NULL.
> Very very strange. I had assumed all along that:
> 1 != NULL
> would be True.
> I could be dumb, but the documentation in SQL did not explain this very
> well. The issues is only with inequality it seems, and doing the
> ANSI_NULLS does not change the issue. So lifes goes... I guess.
>|||Thanks for you input Tibor!
Tibor Karaszi wrote:
> ... So, for a WHERE clause, UNKNOWN will be FALSE. That is the same
> for <> as well as =.
I assumed this too, but the last statement in the sample script shows
something different:
--
Declare @.tmp table (
a int,
b int
)
Insert @.tmp Values (1, 1)
Insert @.tmp Values (2, 3)
Insert @.tmp Values (4, null)
Select * From @.tmp Where !(a = b)
--
Let's see, by row:
1 = 1 : True (negated -> False)
2 = 3 : False (negated -> True)
4 = NULL : UNKNOWN [or False if I understand your post] (negated ->
True)
But the actual result is only row (2, 3), and not (2, 3) AND (4, null)
Just weird, if you ask me.|||You need to either convert the null to something that equality comparisions
work for ie(isnull(b,0) > 0) or use IS NULL AND IS NOT NULL instead of = NULL
and <> NULL if you want to look for values in this field.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Tibor Karaszi" wrote:
> Comparisons against NULL evaluates to the logical state UNKNOWN. But it was deemed impractical for
> the DBMS to raise its virtual hands in the air and say "I don't know" (throw an error) as soon as a
> query has a comparison against NULL. So, for a WHERE clause, UNKNOWN will be FALSE. That is the same
> for <> as well as =. An interesting point is that for a GROUP BY and a constraint, UNKNOWN will be
> TRUE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Daniel Santa Cruz" <dstcruz@.gmail.com> wrote in message
> news:1136492061.330974.56460@.o13g2000cwo.googlegroups.com...
> > This seems very very strange. We thougth it was only an issue with
> > uniqueidentifiers, but we have now determined that any inequality
> > comparison between a value and a null does not result in true or false.
> > The row with that comparison just disappears!
> >
> > Here is a scrip that demos this:
> >
> > Declare @.tmp table (
> > a int,
> > b int
> > )
> >
> > Insert @.tmp Values (1, 1)
> > Insert @.tmp Values (2, 3)
> > Insert @.tmp Values (4, null)
> >
> > Select * From @.tmp
> > -- Returns what I expected
> >
> > Select * From @.tmp Where a = b
> > -- Returns what I expected
> >
> > Select * From @.tmp Where a <> b
> > -- Hmmm... does not return the row (4,null)
> >
> > Select * From @.tmp Where !(a = b)
> > -- This does not return the row (4,null) either
> >
> > This seems to be the case with any inequality comparisons agains NULL.
> > Very very strange. I had assumed all along that:
> > 1 != NULL
> > would be True.
> >
> > I could be dumb, but the documentation in SQL did not explain this very
> > well. The issues is only with inequality it seems, and doing the
> > ANSI_NULLS does not change the issue. So lifes goes... I guess.
> >
>|||Sorry, I have a type on all the scripts
All lines with:
!(a = b)
Should be:
Not (a = b)|||Ryan Powers wrote:
> You need to either convert the null to something that equality comparisions
> work for ie(isnull(b,0) > 0) or use IS NULL AND IS NOT NULL instead of = NULL
> and <> NULL if you want to look for values in this field.
We do that now. The issue was not in figuring out a way around this
"feature" but rather finding this feature. I always assumed (yes, I
assumed!) that equality and inequality results where a complement of
each other... well, seems I was wrong. Equality and inequality
comparisons are only complements of each other if ALL values compared
are non-null. There is the catch.|||> Equality and inequality
> comparisons are only complements of each other if ALL values compared
> are non-null. There is the catch.
Yes, this often causes confusion. We naturally think of predicates
evaluating to true/false rather than true/false/unknown. You need to
consider that:
'not true' can indicate either false or unknown
'not false' can indicate true or unknown
'not unknown' can result in true or false
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Daniel Santa Cruz" <dstcruz@.gmail.com> wrote in message
news:1136495151.092087.185130@.z14g2000cwz.googlegroups.com...
> Ryan Powers wrote:
>> You need to either convert the null to something that equality
>> comparisions
>> work for ie(isnull(b,0) > 0) or use IS NULL AND IS NOT NULL instead of =>> NULL
>> and <> NULL if you want to look for values in this field.
> We do that now. The issue was not in figuring out a way around this
> "feature" but rather finding this feature. I always assumed (yes, I
> assumed!) that equality and inequality results where a complement of
> each other... well, seems I was wrong. Equality and inequality
> comparisons are only complements of each other if ALL values compared
> are non-null. There is the catch.
>|||On 5 Jan 2006 12:52:37 -0800, Daniel Santa Cruz wrote:
>Thanks for you input Tibor!
>Tibor Karaszi wrote:
>> ... So, for a WHERE clause, UNKNOWN will be FALSE. That is the same
>> for <> as well as =.
>I assumed this too, but the last statement in the sample script shows
>something different:
>--
>Declare @.tmp table (
> a int,
> b int
>)
>Insert @.tmp Values (1, 1)
>Insert @.tmp Values (2, 3)
>Insert @.tmp Values (4, null)
>Select * From @.tmp Where !(a = b)
>--
>Let's see, by row:
> 1 = 1 : True (negated -> False)
> 2 = 3 : False (negated -> True)
> 4 = NULL : UNKNOWN [or False if I understand your post] (negated ->
>True)
>But the actual result is only row (2, 3), and not (2, 3) AND (4, null)
>Just weird, if you ask me.
Hi Daniel,
A better way to say it is that rows will be rejected if the WHERE clause
evaluates to FALSE or UNKNOWN. That is, UNKNOWN is not the same as FALSE
(which is how you appear to interpret Tibor), but they are treated the
same -- AFTER evaluating the complete WHERE condition.
Of course, this means that the truth tables for AND, OR and NOT have to
be extended to allow for three values instead of two: TRUE (T), FALSE
(F) and UNKNOWN (U). Here are the three-valued truth tables (use a
non-proportional font to display):
AND | T | U | F OR | T | U | F NOT | T | U | F
--+--+--+-- --+--+--+-- --+--+--+--
T | T | U | F T | T | T | T | F | U | T
U | U | U | F U | T | U | U
F | F | F | F F | T | U | F
Now let's reconsider your last examples:
> 4 = NULL : UNKNOWN [or False if I understand your post] (negated ->
>True)
becomes
4 = NULL : UNKNOWN (negated -> UNKNWON) and hence not included in the
result set.
--
Hugo Kornelis, SQL Server MVP|||> AND | T | U | F OR | T | U | F NOT | T | U | F
> --+--+--+-- --+--+--+-- --+--+--+--
> T | T | U | F T | T | T | T | F | U | T
> U | U | U | F U | T | U | U
> F | F | F | F F | T | U | F
I believe these tables are only accurate when
SET ANSI_NULLS OFF
See the example below.
set ansi_nulls off
select
case
when not (0 = null) then 'true'
else 'false'
end test1
That code above will return 'true' as the test1 column value.
However...
set ansi_nulls on
select
case
when not (0 = null) then 'true'
else 'false'
end test1
Will return 'false' as the test1 column value.
Please correct me if I'm wrong but it seems that if ansi nulls are on
then anything having anything to do with null automatically becomes
null.
Therefore
F AND U -> U (Unlike the table above.)|||On 5 Jan 2006 14:43:06 -0800, Rob wrote:
>> AND | T | U | F OR | T | U | F NOT | T | U | F
>> --+--+--+-- --+--+--+-- --+--+--+--
>> T | T | U | F T | T | T | T | F | U | T
>> U | U | U | F U | T | U | U
>> F | F | F | F F | T | U | F
>I believe these tables are only accurate when
>SET ANSI_NULLS OFF
>See the example below.
(snip)
Hi Rob,
The tables are the same, but the result of (0 = null) is different with
ANSI_NULLS off or on.
Note that the ANSI_NULLS OFF option should only be used to solve
compatibility issues with older applications that were made before SQL
Server complied with ANSI-standard treatment of NULL. And that those
applications should be either replaced or updated to observe ANSI
standard NULL behaviour as soon as feasible. Allowing non-ANSI treatment
of NULLS is a maintenance disaster waiting to happen, if only because
less and less programmers and DBAs are familiar with the old non-ANSI
behaviour and nobody expects it anymore.
--
Hugo Kornelis, SQL Server MVP|||> A better way to say it is that rows will be rejected if the WHERE clause
> evaluates to FALSE or UNKNOWN. That is, UNKNOWN is not the same as FALSE
> (which is how you appear to interpret Tibor), but they are treated the
> same -- AFTER evaluating the complete WHERE condition.
That is a better way of putting it. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info> wrote in message
news:ql4rr1ls73p5rigv4v66je0ljqj5ndml6b@.4ax.com...
> On 5 Jan 2006 12:52:37 -0800, Daniel Santa Cruz wrote:
>>Thanks for you input Tibor!
>>Tibor Karaszi wrote:
>> ... So, for a WHERE clause, UNKNOWN will be FALSE. That is the same
>> for <> as well as =.
>>I assumed this too, but the last statement in the sample script shows
>>something different:
>>--
>>Declare @.tmp table (
>> a int,
>> b int
>>)
>>Insert @.tmp Values (1, 1)
>>Insert @.tmp Values (2, 3)
>>Insert @.tmp Values (4, null)
>>Select * From @.tmp Where !(a = b)
>>--
>>Let's see, by row:
>> 1 = 1 : True (negated -> False)
>> 2 = 3 : False (negated -> True)
>> 4 = NULL : UNKNOWN [or False if I understand your post] (negated ->
>>True)
>>But the actual result is only row (2, 3), and not (2, 3) AND (4, null)
>>Just weird, if you ask me.
> Hi Daniel,
> A better way to say it is that rows will be rejected if the WHERE clause
> evaluates to FALSE or UNKNOWN. That is, UNKNOWN is not the same as FALSE
> (which is how you appear to interpret Tibor), but they are treated the
> same -- AFTER evaluating the complete WHERE condition.
> Of course, this means that the truth tables for AND, OR and NOT have to
> be extended to allow for three values instead of two: TRUE (T), FALSE
> (F) and UNKNOWN (U). Here are the three-valued truth tables (use a
> non-proportional font to display):
> AND | T | U | F OR | T | U | F NOT | T | U | F
> --+--+--+-- --+--+--+-- --+--+--+--
> T | T | U | F T | T | T | T | F | U | T
> U | U | U | F U | T | U | U
> F | F | F | F F | T | U | F
> Now let's reconsider your last examples:
>> 4 = NULL : UNKNOWN [or False if I understand your post] (negated ->
>>True)
> becomes
> 4 = NULL : UNKNOWN (negated -> UNKNWON) and hence not included in the
> result set.
> --
> Hugo Kornelis, SQL Server MVP