Oracle NVL with empty string

Question

I have this table where NULL is the NULL value, not the string NULL:

MYCOL
--------
NULL
example

Why does this query not return the NULL row?

select * from example_so where nvl(mycol, '') = '';

Durative

Answer 1

'' is again NULL in Oracle, because Oracle doesn't support empty Strings just like Other High Level languages or DBMS..

You need to look for NULL/empty string using IS NULL or IS NOT NULL

No other relational operator work against NULL, though it is syntactically valid. SQLFiddle Demo

It has to be,

select * from example_so where mycol IS NULL

EDIT: As per Docs

Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.

Walworth

How come '' is NULL? Isn’t '' the empty string? – Durative

Because an empty string is NULL in Oracle. See my demo in the answer. – Entanglement

Strange, I can’t seem to find this in the documentation. – Durative

I accepted your answer as it answers my question. But note that relational operators do work with NULL. It is quite touchy though. – Durative

@Durative added some sql to fiddle link here.. as demo.. SQL runs successfully, but the result of the expression would be UNKNOWN .. the docs I referred here show the same. – Walworth

AND and OR can be used with NULL to give useful (that is not NULL) results. – Durative

OR will suppress any index usage. – Entanglement

Most hypocritical piece of documentation i've ever seen. Basically saying "You shouldn't treat empty string as NULL. But the DB does. But might change in the future. But we don't know when. And you can't control or change the behavior. But don't treat them the same." – Bullate

Answer 2

Because NULL = NULL is simply unknown. A third state perhaps? It is neither TRUE nor FALSE.

Oracle considers an EMPTY STRING as NULL.

nvl(mycol, '') makes no real sense, as you are making the NULL back to NULL and comparing it again with NULL.

SQL> WITH DATA AS(
  2  SELECT 1 val, 'NULL' str FROM dual UNION ALL
  3  SELECT 2, NULL str FROM dual UNION ALL
  4  SELECT 3, '' str FROM dual UNION ALL
  5  SELECT 4, 'some value' str FROM dual)
  6  SELECT val, NVL(str, 'THIS IS NULL') FROM data WHERE str IS NULL
  7  /

       VAL NVL(STR,'THI
---------- ------------
         2 THIS IS NULL
         3 THIS IS NULL

SQL>

Entanglement

Answer 3

select * from example_so where nvl(mycol, '') = '';

nvl(mycol, '') will be resulted as NULL and when you compared NULL with empty string it cant be compared

create table t(id varchar2(2));
insert into t values (nvl(null,''));   <------ this will insert NULL
insert into t values (nvl(null,'et'));

Cypripedium