What is Different Between Empty String and Null value in Oracle?

[amolc]

Hi,
Can you Please Tell me What is difference between NULL string and EMPTY string?

[michaelb]

Going by the SQL standard the difference is very significant:
NULL means no-value
Since NULL is a big void it cannot be really compared to any other value, including an empty string,
About the only way to capture the NULL is to say

WHERE some_field IS NULL
-- or
WHERE some_field IS NOT NULL
 

Empty string is just that: '' (quote-quote)
For most parts you can treat it just like another character value:

select * from people WHERE lastname = '' ;
-- or
select * from people WHERE lastname != '' ;
 

Oracle very much blurred the line between NULL and the empty string,
making the latter almost an alias for NULL
The following two statements produce identical results:

update people set lastname = ''
update people set lastname = NULL
 

which results in something very counter-intuitive:

UPDATE people set lastname = '' ;
6 rows updated.
 
SELECT * FROM people where lastname = '' ;
no rows selected
 
SELECT * FROM people where lastname is NULL ;
6 rows selected
 

There are many articles solely dedicated to the concept of NULL in the databases and people have very opposite opinions on whether we even need them and how they should be treated.
I almost expect that some Oracle experts (I am not the one) will jump in and add some interesting information here.
And I'm sure you'll find tons of it if you just search for "database null" in Google.

Here's one man page on NULLS in Oracle