Oracle/PLSQL: Difference between an Empty String and a Null value

Question:
What is the difference between an "empty" value and a "null" value? 
When I select those fields that are "empty" versus "null", I get two different result sets.

Answer:
An empty string (i.e. length zero) is treated as a null value in Oracle. Let's demonstrate.
We've created a table called suppliers with the following table definition:

create table suppliers
( supplier_id 	number,
supplier_name 	varchar2(100));

Next, we'll insert two records into this table.

insert into suppliers (supplier_id, supplier_name )
  values ( 10565, null );
insert into suppliers (supplier_id, supplier_name )
  values ( 10567, '' );

The first statement inserts a record with a supplier_name that is null, while the second statement inserts a record with an empty string as a supplier_name.
Now, let's retrieve all rows with a supplier_name that is an empty string value as follows:

select * from suppliers
         where supplier_name = '';

When you run this statement, you'd expect to retrieve the row that you inserted above. But instead, this statement will not retrieve any records at all.
Now, try retrieving all supplier_ids that contain a null value:

select * from suppliers
         where supplier_name is null;

When you run this statement, you will retrieve both rows. This is because Oracle has now changed its rules so that empty strings behave as null values.

It is also important to note that the null value is unique in that you can not use the usual operands (=,  <, >, etc) on a null value. Instead, you must use the IS NULL and IS NOT NULL conditions.

Loading