Re: id <> '2'

"mike" <hillmw_at_charter.net> wrote in message news:1127338314.870584.213820_at_g49g2000cwa.googlegroups.com...
>I have a field defined as follows:
>
> id integer
>
> This field normally has some value in it 1,2,3,4, but it can be null.
>
> For example, say I have 10 records, and 5 records have values
> 1,2,3,4,5, but the other 5 are null
>
> If I do a select like:
>
> select *
> from mytable
> where id='2'
>
> I get 1 record.
>
> If I do a select like:
>
> select *
> from mytable
> where id<>'2'
>
> I get 4 records, but shouldn't I be getting 9 records?
>
> How would i change my select to get the 9 records?
>
> Mike
>

NULL is by definition UNKNOWN

so, it is impossible to say that a NULL (UNKNOWN) value is something or is not something; think of a bonus check -- until the check is in your hands, you don't know the amount (could even be negative if it's commisssion against draw), so you can't make any spending plans

in your case, you could use the NVL function

    where nvl(id,-1) <> 2

of course, there are performance issues using a function (or expression) on a column in a predicate -- keep in mind that you would need to use a Function Based Index if you ever need to index ID for performance

couple of other things to consider:

    you shouldn't use quotes around numeric values     why does a column named ID allows nulls? it would it should be mandatory

++ mcs Received on Wed Sep 21 2005 - 17:03:05 CDT