Null Values in SQL query

I have datatable field with values "ABC", " ", and NULL
When I say "select * from tbl_abc where myfield <>"ABC", the query returns
the value " ". However, the field with value = NULL is ignored.
Is there any way, we get the null value also in the query results?
Thanks.select *
from tbl_abc
where myfield <> 'ABC'
OR myfield IS NULL
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"rkbnair" <rkbnair@.discussions.microsoft.com> wrote in message
news:53082B03-92E6-42AF-8241-6B4B55DE613E@.microsoft.com...
> I have datatable field with values "ABC", " ", and NULL
> When I say "select * from tbl_abc where myfield <>"ABC", the query returns
> the value " ". However, the field with value = NULL is ignored.
> Is there any way, we get the null value also in the query results?
> Thanks.|||myfield <> 'ABC' means myfield is not null. So, why should
myfield IS NULL to be specified explicitely?
"Adam Machanic" wrote:
> select *
> from tbl_abc
> where myfield <> 'ABC'
> OR myfield IS NULL
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "rkbnair" <rkbnair@.discussions.microsoft.com> wrote in message
> news:53082B03-92E6-42AF-8241-6B4B55DE613E@.microsoft.com...
> > I have datatable field with values "ABC", " ", and NULL
> >
> > When I say "select * from tbl_abc where myfield <>"ABC", the query returns
> > the value " ". However, the field with value = NULL is ignored.
> >
> > Is there any way, we get the null value also in the query results?
> >
> > Thanks.
>
>|||"rkbnair" <rkbnair@.discussions.microsoft.com> wrote in message
news:0B7BB9AC-B436-4DB6-AE20-4D05A4204ABB@.microsoft.com...
> myfield <> 'ABC' means myfield is not null.
No, it doesn't.
NULL is not equal to anything. Including NULL. In addition, NULL is not
not-equal to anything. Including NULL. The result of any comparison
involving NULL is unknown.
(NULL = <anything>) is unknown.
And (NULL <> <anything>) is also unknown.
And (NULL = NULL) is unknown too!
A predicate is only valid if it resolves to a true condition. (myfield <>
'ABC'), to resolve to true, must be true. If myfield is NULL, it resolves
to unknown, not true -- and therefore the row is not returned.
This is called "three-valued logic" and is quite confusing for a lot of
people just starting with SQL. It's one reason that I recommend that NULLs
should be used as little as possible. Instead of using NULLs, I advocate
the use of "missing value tokens" -- well defined tokens that you can put in
for missing values when you'd otherwise use a NULL. For instance, an empty
string, or the string 'Value Undefined'. These can be domain-specific; so
if you're dealing with postal codes, your unknown value token might be
'00000'; if you were dealing with an author biography, your token might be,
'Biography not on record'.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--