---

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 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.

With respect to using null values in SQL statements in Oracle, 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.