Re: Easy SQL problem

Also, just for clarification, a null (unknown value) is not the same thing as an empty string (""), at least in Oracle. Most new users are confused by this. It relates to there being three states for a check box; Checked, Unchecked, and Never Checked. Unchecked is equal to an empty string, and Never Checked is NULL (depending, of course, on the prgramming environment and tool).

"Richard Foote" <Richard.Foote_at_oracle.com> wrote in message news:3D5327ED.AC92D4DF_at_oracle.com...
> 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
Received on Fri Aug 09 2002 - 08:14:29 CDT