Re: Easy SQL problem

Hi Ben,

The nice thing about an easy SQL problem is that generally it's an easy SQL solution.

What you are highlighting is not a 9i feature but a characteristic of how Oracle deals with nulls since time began.

Logically, the best way to view a null is as an unknown. Therefore if you ask if one unknown value is equal to (or not equal to) another unknown value the answer of course is you don't know.

Therefore a condition variable = null can never be treated as true and will never return a row.

Neither will variable <> null.

Therefore, if you want to know if a column is (or is not) null you must use the 'is null' (or 'is not null') notation.

Cheers

Richard
Ben wrote:
>
> Any one know if this is a new 9i feature or not?
>
> I have the following table.
>
> SQL> desc table1
> Name Null? Type
> ----------------------------------------------------- --------
> -----------
> ID
> VARCHAR2(5)
> NAME
> VARCHAR2(5)
>
> SQL> insert into table1 values ('242','');
>
> 1 row created.
>
> SQL> select * from table1;
>
> ID NAME
> ----- ------------------------------
> 1 A
> 2 A
> 3 C
> 4 D
> 5. E
> aaa C
> 242
>
> 7 rows selected.
>
> SQL> select * from table1 where name is null;
>
> ID NAME
> ----- ------------------------------
> 242
>
> SQL> select * from table1 where name is not null;
>
> ID NAME
> ----- ------------------------------
> 1 A
> 2 A
> 3 C
> 4 D
> 5. E
> aaa C
>
> 6 rows selected.
>
> SQL> select * from table1 where name <> '';
>
> no rows selected
>
> SQL> select * from table1 where name = '';
>
> no rows selected
>
> My question is why do I get no rows selected for "select * from table1
> where name <> '';" and "select * from table1 where name = '';"?
>
> Any idea?
>
> Thanks!!
> Ben