DIFFERENCE BETWEEN AN EMPTY STRING AND A NULL VALUE IN ORACLE

An empty string 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 records where the supplier_name contains 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.