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.