Re: Easy SQL problem

From: Andy Hassall <andy_at_andyh.org>
Date: Sat, 10 Aug 2002 16:25:57 +0100
Message-ID: <rgbaluci0ihsptatjqbdsm5gsrcre3qjqn@4ax.com>

On Fri, 9 Aug 2002 09:14:29 -0400, "Alan" <alanshein_at_erols.com> wrote:

>>> 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 = '';"?
>>
>> 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.
>
>Also, just for clarification, a null (unknown value) is not the same thing
>as an empty string (""), at least in Oracle.

It shouldn't be, but it _is_ in Oracle - that's what's causing the confusion.

> 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).

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production

SQL> create table test (ch varchar2(32) null);

Table created.

SQL> insert into test values('a');

1 row created.

SQL> insert into test values('');

1 row created.

SQL> insert into test values(NULL);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test where ch is null;

2 rows selected.

SQL> select * from test where ch is not null;

1 row selected.

And just to show that empty string is NULL in Oracle:

SQL> select * from dual where '' is null;

D
-
X

1 row selected.

Empty string being NULL is being deprecated; it's in one of the 9i new features sections in the docs. I was under the impression that they were going to fix it in 9i, but it's just gone in as a warning that it may be fixed in the future.

-- 
Andy Hassall (andy@andyh.org) icq(5747695) http://www.andyh.org
http://www.andyhsoftware.co.uk/space | disk usage analysis tool

Received on Sat Aug 10 2002 - 10:25:57 CDT