Bit column with NULL

I have this table called Location. There is a column called IsInactive. The
value can contain a NULL,0, or 1.
Why does this statement work:
SELECT * FROM Location WHERE IsInactive <> 1
I have 3 location records where IsInactive = NULL and it doesn't return
them.
Just curious.
DavidNULL is not <> 1
This evaluates to Unknown.
Try:
SELECT * FROM Location WHERE IsInactive = 0 or IsInactive is null
Or:
SELECT * FROM Location WHERE isnull(IsInactive, 0) = 0
"David Pope" <dpope@.nospam.satx.rr.com> wrote in message
news:uUDKvbvAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> I have this table called Location. There is a column called IsInactive.
The
> value can contain a NULL,0, or 1.
> Why does this statement work:
> SELECT * FROM Location WHERE IsInactive <> 1
> I have 3 location records where IsInactive = NULL and it doesn't return
> them.
> Just curious.
> David
>|||Hello David,
This depends on your ANSI_NULL settings.
If you have ANSI_NULL on, anytime you compare something to a NULL it will
fail. This means that if the value is NULL and you want all rows that <>
1, then it won't be returned.
For more information read up on SET ANSI_NULLS in BOL.
http://msdn.microsoft.com/library/d... />
t_7g32.asp
Aaron Weiker
http://aaronweiker.com/

> I have this table called Location. There is a column called
> IsInactive. The value can contain a NULL,0, or 1.
> Why does this statement work:
> SELECT * FROM Location WHERE IsInactive <> 1
> I have 3 location records where IsInactive = NULL and it doesn't
> return them.
> Just curious.
> David
>|||NULL is basically unknown or not yet filled in...
So the systems response to anything >, =, < or != is never going to respond
with the TRUE.
to get your statement to work try...
SELECT * FROM Location WHERE isnull(IsInactive, 0) <> 1
look up isnull in books on-line (BOL) for more specific information.
Ryan
"David Pope" wrote:

> I have this table called Location. There is a column called IsInactive. Th
e
> value can contain a NULL,0, or 1.
> Why does this statement work:
> SELECT * FROM Location WHERE IsInactive <> 1
> I have 3 location records where IsInactive = NULL and it doesn't return
> them.
> Just curious.
> David
>
>|||1) NULLs do not compare to anything. This is absolute foundations, not
fancy stuff. You should know the rules for NULL before you ever wrote
any code.
2) A proper data model does not use flags; that is assembly language
programming. There are no BOOLEAN types in SQL-92 for a good reason,
having to do with NULLs and 3VL.
For example, your data might want to store the date range that
something a location was active.
Oh, unless you have only one of them, the table name ought to be
"Locations" -- it is set and not a scalar.|||
--CELKO-- wrote:

>1) NULLs do not compare to anything. This is absolute foundations, not
>fancy stuff. You should know the rules for NULL before you ever wrote
>any code.
>2) A proper data model does not use flags; that is assembly language
>programming. There are no BOOLEAN types in SQL-92 for a good reason,
>having to do with NULLs and 3VL.
>
There is a BOOLEAN type in SQL-99. That's not to say we'd ever get
used to a type where NOT(Unknown) is Unknown.
Steve Kass
Drew University

>For example, your data might want to store the date range that
>something a location was active.
>Oh, unless you have only one of them, the table name ought to be
>"Locations" -- it is set and not a scalar.
>
>|||I will tell the developers at Olero Software. =0}
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1106751641.483267.271940@.c13g2000cwb.googlegroups.com...
> 1) NULLs do not compare to anything. This is absolute foundations, not
> fancy stuff. You should know the rules for NULL before you ever wrote
> any code.
> 2) A proper data model does not use flags; that is assembly language
> programming. There are no BOOLEAN types in SQL-92 for a good reason,
> having to do with NULLs and 3VL.
> For example, your data might want to store the date range that
> something a location was active.
> Oh, unless you have only one of them, the table name ought to be
> "Locations" -- it is set and not a scalar.
>