does oracle recognize null as a value ???

[misticawi]

i have tried looking thru documentation but have not found anything...i know that null is seen as an unknown value and you can specify null in the where clause when creating a select in sql...but does oracle recognize null as a value ? i believe it does because you can use it in the where clause but i am not sure...if anyone has any thoughts i would appreciate it...thanx

[Tarry]

Read this thread...........
http://www.dbasupport.com/forums/sho...?threadid=8666

[KenEwald]

Here some stuff I've learned. Null "values" are treated differently in SQL and PL/SQL.

"where COLUMN is null" works in SQL and SQL within a PL/SQL block.

"if :value is null" in a PL/SQL block is not a predictable evaluation. (at least I haven't been able to produce wide scale predictable results - and I've been bitten a time or two)

Here's something that ALWAYS works in SQL and PL/SQL:

FIELD||'$$$' = '$$$' if checking for a null
FIELD||'$$$' != '$$$' if checking for -not- null

Sidelines .. "is null" and "is not null" will not pick up an index, "FIELD||'$$$' = '$$$' won't either. On the other hand, it's easier to type "is null" .. just don't use it in PL/SQL.

-Ken

[chrisrlong]

Originally posted by KenEwald
"if :value is null" in a PL/SQL block is not a predictable evaluation. (at least I haven't been able to produce wide scale predictable results - and I've been bitten a time or two)

Odd. I've never seen or heard of any issues with this and I use it all the time.

- Chris

[KenEwald]

It's the use of "="... sorry, not "is null"

Try this:
-----------
declare
myval_1 varchar2(10) := null;
myval_2 varchar2(10) := null;
begin
if :myval_1 = :myval_2 then
dbms_output.put_line('null equals null');
else
dbms_output.put_line('null does not equal null?');
end if;
end;
-----------

Returns "null does not equal null?"

You cannot compare values that may contain a NULL value using the equal sign ( "=" ) equality operator or not equal ("<>", "!=") inequality operator.

-Ken

[Edited by KenEwald on 03-20-2002 at 11:48 AM]

[chrisrlong]

Yes. Absolutely true.

- Chris

[jmodic]

However this behavior is not unique to PL/SQL, it is exactly the same in SQL. It's an universal rule (well, with some exceptions as allways ): NULL is never equal (=) to NULL, nor it is ever not equal (!=) to NULL.

So I still can't see where your statement "Null values are treated differently in SQL and PL/SQL" could prove to be true.

[chrisrlong]

And to take it the final step, any logical operator with NULL will always return false. And this behaviour is true, as Jurij points out, in all languages that handle 3-valued logic.

- Chris

[KenEwald]

Thanks for corrections.

Bottom line... (from my original post on this thread)

FIELD||'$$$' = '$$$' if checking for a null
FIELD||'$$$' != '$$$' if checking for -not- null