Comparing columns containing null values
-
Hi.
Can anyone explain why select 1 below returns no rows. I would expect that when ansi_nulls is set to off, then "null equals null", such that select 1 would return one row, as select 2 below.
Thanks in advance,
Jesper
set ansi_nulls off
go
create table #temp(a int null)
go
insert into #temp select null
-- select 1 returns no rows
select * from #temp where a = a
-- select 2 returns one row
select * from #temp where a = null
drop table #temp
go
-
I don't think that it's designed that way...
For example 1, you have a comparison between datavalue = datavalue, which by design should always evaluate to null, if either of the values are null. This is also the expected behaviour imo, else inner joins and such would start acting really funny. I would never expect this example to return any rows.
For example 2, you have a comparison between datavalue = explicit null, which is a valid test for null values. I'm sure you're aware of that 'where a = null' is 'wrong' - you should use 'where a is null', which is the 'correct' syntax when testing for null values.
Out of curiosity - is this a pure academic question, or is there some 'real' purpose behind it?
/Kenneth
-
I think I have read somewhere in the help that while you can compare by using "is null", there is in fact no definite value for Null.
In example 1 "Where a=a" is effectively asking for an exact value match, however, no two nulls will ever have the same value so the comparison fails and you get no rows returned.
Wow, that sounds technical.
-
Kenneth, thanks for the reply. I am aware of the correct "is null" syntax, I was only trying to stress the equality between examples 1 and 2.
The way I have stated it, it is purely an academic question - it's a simplification of a real problem I came across, which (again simplified) is the following: Say you have two nullable int columns a and b, and you want to find all rows for which they have the same value, including the case where both are null. Then I expected that I - with ansi_nulls off (i.e, simplified, "null equals null") - could write
where a=b.
Instead I should write the rather silly
where a = b or (a = null and b = null)
Or even better, since it also applies in the case of ansi_nulls on
where a = b or (a is null and b is null)
Or maybe (if a and b are always strictly positive)
where isnull(a, 0) = isnull(b, 0)
-
"where a = null", isn't that asking for an exact value match as well...?
-
NULL can never be compared to anything, it is like infinity you can never be equal to infinity (< or > either)
if you want to evaluate NULL values to be the same use isnull() function like isnull(columnContainingNullValues,'') and the zero length string (zls) will replace the null values and be evaluated. NB nulls will then be evaluated to rows that previously contained zls so you may want to use a different replacement string.
if you want to explicity search for nulls you must say
WHERE columnContainingNullValues IS NULL
or
WHERE columnContainingNullValues IS NOT NULL
I hope that helps
-
I fully agree - if ansi_nulls is set to on. The point is that I have set ansi_nulls to off, which allows me to use null as a value in certain cases (as an example, run my code from the initial post in qa).
-
I don't think that the ansi setting applies in this case. Imagine what would happen if it would..
Take your everyday inner join:
select foo from bar a join foobar b on a.col1 = b.col1
...and assume you have rows where col1 contains null in both tables..
If they would to produce matches based on ansi settings.. *shiver*
We would start getting all kinds of funky results all over the place.For your example, if you need to compare two columns for data or null match, you need to exchange nulls to something 'comparable' - ie anything not null that you can do comparative tests on - just as you've showed.
/Kenneth
-
Good point on the joins. Although you could write
select foo from bar a join foobar b on a.col1 = b.col1 and a.col1 is not null
this is quite cumbersome, especially if you are joining several columns that could be null.
"null equals null" matches is probably not what you want most of the time when you are comparing columns (with ansi_nulls off), so I guess it's OK. I just never thought about it and was surprised when I ran my (simple) example from the initial post.
I have only found "column equals null" examples in BOL (for ansi_nulls off), no "column equals column" examples, but that is probably because the latter doesn't depend on the ansi_nulls setting.
-
Also, the ansi settings is a session setting, so imagine the chaos if col = col would behave differently depending on each connections specific settings..
Actually, never thought much about it, it's just 'how it works', especially when you think about inner joins - outer joins does sort of give you col = col where one is null, and full joins where either is null, but that's a different story.
/Kenneth
-
Well, this is actually the case if you write col = null. Or (more likely) col = @var where @var can be null...
-
NULL mens "don't know".
For you my salary is NULL.
As well as salary of my wife.
Let @Salary1 = My Salary = NULL;
@Salary2 = My Wife's Salary = NULL
Now answer me:
If @Salary1 = @Salary2 ?
If @Salary1 @Salary2 ?
_____________
Code for TallyGenerator -
To answer this, I need to know whether ansi_nulls is on or off
-
Forget about SQL.
Just give me an answer on simple question:
My Salary = My Wife's Salary: TRUE or FALSE?
My Salary My Wife's Salary: TRUE or FALSE?
One RIGHT answer for each question, please. Acceptable for real life.
Now about ANSI_NULLS.
There is another question.
You have list of people joined to the list of kids.
Some people don't have kids, so left join will return you NULL as Child's name for such people.
Now you need to find all pairs of adults with kids havind the same name.
Will you be happy to see people not having kids at all in this list?
So, before turn ANSI_NULL OFF count the risk of being fired because of wrong results coming from your queries.
_____________
Code for TallyGenerator -
When we specify the column names, the sql will not count the null values even if the ansi_null off
select count(*) from #temp = 1
select count(a) from #tmp = 0
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply