Oracle empty string converts to null

This says it all:

select NVL('','it is null') as value
from dual;

SQL Fiddle

2 things:

1) '' gets converted to NULL on insert. That's an Oracle VARCHAR2 thing.

2) select * from test where f=''; is trying to do select * from test where f=NULL, which isn't defined, and will return nothing because NULL doesn't like the equality operator. You have to use IS NULL or IS NOT NULL.

I'll add that the CHAR datatype behaves differently because it is padded.

Oracle treats '' and NULL the same. When inserting '', there is no conversion of '' to NULL, merely an interpretation of '' as NULL in the same way that the word NULL is interpreted as NULL or rtrim('a','a') is interpreted as NULL.

Here is a demonstration using the following table and insert:

drop table t1;
create table t1 (c1 varchar2(10));
insert into t1 (c1) values ('');

The insert above inserted a NULL value for c1. You can select that row as follows:

SELECT c1 FROM t1;

When you add a WHERE clause to compare equality and one of the values being compared is NULL, the result will always be unknown. Unknown will evaluate to false except that further operations on an unknown value produce unknown values. All of the following return no rows because the WHERE clauses contain conditions that will never be true regardless of the data.

SELECT c1 FROM t1 WHERE c1 = '';
SELECT c1 FROM t1 WHERE c1 = NULL;
SELECT c1 FROM t1 WHERE '' = '';
SELECT c1 FROM t1 WHERE NULL = NULL;

Oracle provides a special syntax to retrieve rows with a particular column having null values -- IS NULL.

SELECT c1 FROM t1 WHERE c1 IS NULL;

There are a few conditions in which oracle compares NULLS treating them as equal to other NULL values such as in DECODE statements and in compound keys.

More information can be found in the SQL Language Reference.