Re: sql question...

Hi Peggy,

A null value is in effect an unknown value, therefore nothing can ever be matched to it,

i.e. no expression can equal a null column, no expression can 'not equal' a null column, a null column can not match a null column.

You can however tell Oracle what value to assume for a null column with the NVL function, which used in your #1 case would make it read :-

select lname, fname
from resdemo
where nvl(fellow,'X') != 'Y'

this query would then find all rows as in your version plus any rows where "fellow" was null.

I hope this is useful.

Paul.

oispeggy_at_acsu.buffalo.edu wrote in article <Pine.GSO.3.96.970612151549.25962D-100000_at_xena.acsu.buffalo.edu>...
>
> Learn something new every day....
>
> 1.
> select lname, fname This returns everyone with 'N' or 'G' or '
 '.
> from resdemo None with 'Y'. None with null.
> where fellow != 'Y'
>
> 2.
> select lname, fname This returns only those with null.
> from resdemo
> where fellow is null
>
> 3.
> select lname, fname This returns only those with ' '.
> from resdemo
> where fellow = ' '
>
>
> What surprised me is that #1 did not return anyone with a null value?
> I thought it would? Why didn't it?
>
> Thanks,
>
> - Peggy -
> oispeggy_at_acsu.buffalo.edu
>
>
Received on Sun Sep 28 1997 - 00:00:00 CDT