What is the Difference between an empty string and a null value in Oracle

 Difference between an empty string and a null value in Oracle:

What is the Difference between an empty string and a null value

--------------------------------------------------------------------------------

When we select those fields that are "empty" versus "null", we get two different result sets.



Answer: An empty string is treated as a null value in Oracle. Let's demonstrate.

Lets create a table called employee with the following table definition:

create table employee
( employee_id number,
employee_name varchar2(100));

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

insert into employee (employee_id, employee_name ) values ( 10565, null );

insert into employee (employee_id, employee_name ) values ( 10567, '' );

The first statement inserts a record with a employee_name that is null, while the second statement inserts a record with an empty string as a employee_name.

Now, let's retrieve all rows with a employee_name that is an empty string value as follows:

select * from employee where employee_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 employee_ids that contain a null value:

select * from employee where employee_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


SQL> create table employee
( employee_id number,
employee_name varchar2(100));  2    3

Table created.

SQL> insert into employee (employee_id, employee_name ) values ( 10545, null );

1 row created.

SQL> commit;

Commit complete.

SQL> insert into employee (employee_id, employee_name ) values ( 10555, '' );

1 row created.

SQL> commit;

Commit complete.

SQL> select * from employee where employee_name = '';

no rows selected

SQL> select * from employee where employee_name is null;

EMPLOYEE_ID   EMPLOYEE_NAME
--------------------------------------------------------------------------------
      10545


      10555