Oracle NVL with an empty string

Question

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

 MYCOL -------- NULL example 

Why doesn't this query return a NULL string?

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

Answer 1

'' again NULL in Oracle, because Oracle does not support empty strings in the same way as other high-level languages ​​or DBMS.

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

No other relational operator works with NULL , although it is syntactically valid. SQLFiddle Demo

It should be,

 select * from example_so where mycol IS NULL 

EDIT: By Docs

Oracle Database currently considers a character value with a length from zero to zero. However, this cannot continue in future releases, and Oracle recommends that nulls not be treated as empty strings.

Answer 2

Because NULL = NULL simply unknown. Perhaps a third state? It is not TRUE or FALSE .

Oracle treats EMPTY STRING as NULL.

nvl(mycol, '') makes no real sense, since you are returning NULL 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> 

Answer 3

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

nvl (mycol, '') will be displayed as NULL and when you compared NULL with an empty string, it cannot 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'));