11

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, '') = '';
Maheswaran Ravisankar
  • 17,652
  • 6
  • 47
  • 69
kmkaplan
  • 18,655
  • 4
  • 51
  • 65

3 Answers3

20

'' 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.

Maheswaran Ravisankar
  • 17,652
  • 6
  • 47
  • 69
  • How come `''` is `NULL`? Isn’t `''` the empty string? – kmkaplan Oct 09 '14 at 09:26
  • Because an empty string is NULL in Oracle. See my demo in the answer. – Lalit Kumar B Oct 09 '14 at 09:28
  • Strange, I can’t seem to find this in the documentation. – kmkaplan Oct 09 '14 at 09:35
  • I accepted your answer as it answers my question. But note that relational operators *do* work with `NULL`. It is quite touchy though. – kmkaplan Oct 09 '14 at 09:46
  • @kmkaplan added some sql to [fiddle link here](http://sqlfiddle.com/#!4/d41d8/36233).. as demo.. SQL runs successfully, but the result of the expression would be `UNKNOWN` .. the docs I referred here show the same. – Maheswaran Ravisankar Oct 09 '14 at 09:51
  • 1
    `AND` and `OR` can be used with `NULL` to give useful (that is not `NULL`) results. – kmkaplan Oct 09 '14 at 09:56
  • `OR` will suppress any index usage. – Lalit Kumar B Oct 09 '14 at 10:16
  • 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." – cautionbug Nov 22 '17 at 15:11
8

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>
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
0

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'));
user3380585
  • 163
  • 1
  • 12