Null values not returns when "Not in List" used on column

Greetings,
I am running MS SQL 2000 here. We've just found that queries with "not
in list" where clauses do not return the rows that have NULL values in
the column that the "not in list" clause pertains to.
For instance, let's say the column is Auto.makers. The column has the
values of Ford, Porche, VW, and a few NULL. If you run a:
Select *
where Auto.makers NOT IN LIST VW
It would return all rows with Ford and Porche, but it will not return
NULLs.
At first I thought it was the reporting software we are using, but then
I ran the query with query analyzer itself and got the same behavior.
Is this normal SQL behavior. Besides adding another where clause to
include NULLs, is there a way on the database side to force NULLs to be
returned in such circumstances?
Thanks.
PaulYes, that is normal behavior. When you do a comparison, it is compared
against known values.
NULL represents UNKNOWN values, and therefore cannot be compared.
You should add:
OR Auto.Makers IS NULL
to your WHERE clause.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Paul" <pauldi@.iona.com> wrote in message
news:1159477112.303885.194920@.i3g2000cwc.googlegroups.com...
> Greetings,
> I am running MS SQL 2000 here. We've just found that queries with "not
> in list" where clauses do not return the rows that have NULL values in
> the column that the "not in list" clause pertains to.
> For instance, let's say the column is Auto.makers. The column has the
> values of Ford, Porche, VW, and a few NULL. If you run a:
> Select *
> where Auto.makers NOT IN LIST VW
> It would return all rows with Ford and Porche, but it will not return
> NULLs.
> At first I thought it was the reporting software we are using, but then
> I ran the query with query analyzer itself and got the same behavior.
> Is this normal SQL behavior. Besides adding another where clause to
> include NULLs, is there a way on the database side to force NULLs to be
> returned in such circumstances?
> Thanks.
> Paul
>|||"Paul" <pauldi@.iona.com> wrote in message
news:1159477112.303885.194920@.i3g2000cwc.googlegroups.com...
> Greetings,
> I am running MS SQL 2000 here. We've just found that queries with "not
> in list" where clauses do not return the rows that have NULL values in
> the column that the "not in list" clause pertains to.
> For instance, let's say the column is Auto.makers. The column has the
> values of Ford, Porche, VW, and a few NULL. If you run a:
> Select *
> where Auto.makers NOT IN LIST VW
> It would return all rows with Ford and Porche, but it will not return
> NULLs.
> At first I thought it was the reporting software we are using, but then
> I ran the query with query analyzer itself and got the same behavior.
> Is this normal SQL behavior. Besides adding another where clause to
> include NULLs, is there a way on the database side to force NULLs to be
> returned in such circumstances?
> Thanks.
> Paul
>
Firstly, it isn't entirely clear just what query you ran or where your nulls
are because the pseudo-code you posted is not a valid SQL statement. It
always helps to post real code.
Secondly, null never equals null. That's just the way SQL works. If you are
sensible about design then you should avoid using nulls in any way that
forces you to write needlessly complex queries to work around them. Since
you apparently need the null values to be equal to each other one might
conclude that the person who designed your table didn't think properly about
your reporting needs.
Lookup "three-value logic" in Books Online to read about how nulls work.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On 28 Sep 2006 13:58:32 -0700, Paul wrote:
(snip)
>For instance, let's say the column is Auto.makers. The column has the
>values of Ford, Porche, VW, and a few NULL. If you run a:
>Select *
>where Auto.makers NOT IN LIST VW
>It would return all rows with Ford and Porche, but it will not return
>NULLs.
(snip)
Hi Paul,
In addition to the replies by Arnie and David -- would you be willing to
wager a bet that the maker of my car is NOT IN ('Ford','Porsche','VW')?
That is what you seem to expect of your DB. NULL means "no value here".
Nothing more, nothing less. It doesn't mean "has no car", or "self-made
car", or anything else - just "no value here". The DB, like you, has no
idea if I have any car and if so, what make it is - and yet, you expect
the DB to say that the maker of my car is not Ford, Porshe, or VW.
Hugo Kornelis, SQL Server MVP|||Arnie,
Thanks. That is what I have been telling them to do. I just recently
picked up any responsability for this database which is an extract from
the database our SAP system runs on top of. Unfortunately for me, SAP
allows them to use NULL values in fields they are attempting to report
from.
Hugo and Arnie,
That clears it up a bit, and this is how I initially explained it to my
users. As usual, they weren't satisfied with the answer since it "just
should work" they way they want it to.
Thanks all.
Paul