Re: SQL NULL vs '' problem

Oracle will treat '' and null the same however, Oracle treats a NULL as unknown therefore even if you are comparing two columns that are null they will not evaluate as equal. That is why the IS NULL returns a count of 1.

Kenny Gump

mikep_at_chemweb.com wrote in message <7mvija$43$1_at_nnrp1.deja.com>...
>There is either a serious bug in Oracle or I misunderstand how
>SQL/Oracle works (I assume the latter!). I expect the first count to
>be at least 1, and the second count to be 0:
>
>SQLWKS> update my_table
> 2> set
> 3> my_table_row='' where my_other_table_row='jsmith';
>1 row processed.
>SQLWKS> select count(*) from usr where my_table_row='';
>COUNT(*)
>----------
> 0
>SQLWKS> select count(*) from usr where my_table_row is null and
>my_other_table_row='jsmith';
>COUNT(*)
>----------
> 1
>1 row selected.
>
>
>Even after a commit, you get the same count results. The value of
>my_table_row before was a variety of values. I thought '' and NULL
>were different values, so why does it set it to NULL and even if they
>were the same value, why does a count of my_table_row='' give 0?
>
>btw I am using
>Oracle7 Server Release 7.3.3.4.0 - Production Release
>With the distributed and parallel query options
>PL/SQL Release 2.3.3.4.0 - Production
>running on NT4
>
>Thanks for any help
>
>Mike
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
Received on Mon Jul 19 1999 - 14:52:18 CDT