records with <NULL> aren't returned?

I have a table (TempTable) with one field (Field1)
and three records:
2
3
<NULL>
if I do " Select * from TempTable where Field1 <> '2' "
I only get returned :
3
why didn't the <NULL> record show up? Can anything be done to the
table properties without having to modify the query (by adding 'OR
Field1 IS NULL')
Thanks...No, Not as far as I know
SELECT * FROM TempTable
WHERE field1 <>1
OR field1IS NULL
or
SELECT * FROM TempTable
WHERE COALESCE(field1,0) <> 1
and take a look at these 2 queries
SELECT COUNT(*) FROM TempTable --3
SELECT count(field1) FROM TempTable --2
http://sqlservercode.blogspot.com/|||The reason for this is that both = null and <> null are undefined. SQL will
return false in both cases, as the convention for undefined.
For example, null = null is undefined as thus returns false.
There is no way that I know to change this behavior.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Eych" wrote:
> I have a table (TempTable) with one field (Field1)
> and three records:
> 2
> 3
> <NULL>
> if I do " Select * from TempTable where Field1 <> '2' "
> I only get returned :
> 3
> why didn't the <NULL> record show up? Can anything be done to the
> table properties without having to modify the query (by adding 'OR
> Field1 IS NULL')
> Thanks...
>|||The behavior you are seeing is the ANSI standard. It is recommended that
you use this, but if you need to turn it off , you can do so at the
connection level.
SET ANSI_NULLS OFF
--
Phil Lavene
"Eych" <eycheych@.hotmail.com> wrote in message
news:1137085275.394772.87080@.g47g2000cwa.googlegroups.com...
>I have a table (TempTable) with one field (Field1)
> and three records:
> 2
> 3
> <NULL>
> if I do " Select * from TempTable where Field1 <> '2' "
> I only get returned :
> 3
> why didn't the <NULL> record show up? Can anything be done to the
> table properties without having to modify the query (by adding 'OR
> Field1 IS NULL')
> Thanks...
>