What is the difference between '' and null in sql

Question

There are 2 simple queries

--Query returns results count > 0
select 1 from ordine where DateField between nvl(null, to_date('01/01/1900', 'dd/mm/yyyy')) and nvl(null, to_date('31/12/2999', 'dd/mm/yyyy'));

--Query returns results count = 0
select 1 from ordine where DateField between nvl('', to_date('01/01/1900', 'dd/mm/yyyy')) and nvl('', to_date('31/12/2999', 'dd/mm/yyyy'));

Only difference between these 2 queries is that One is having empty string('') and other is having null.

I thought that it could be possible '' is not equal to null in sql. So I tried following query:

select  nvl(null, to_date('01/01/1900', 'dd/mm/yyyy')), nvl(null, to_date('31/12/2999', 'dd/mm/yyyy')) from dual;

select  nvl('', to_date('01/01/1900', 'dd/mm/yyyy')), nvl('', to_date('31/12/2999', 'dd/mm/yyyy')) from dual;

It was very strange that both of above queries returned same result. But still I wasn't sure so I tried another query to make sure '' is same as null or not. And I tried following 2 queries:

select  1 from dual where '' is null; /* returned 1 record*/
select  1 from dual where '' is not null; /* returned 0 records*/

At this point I was so confused, I have now following questions
Is '' equal to null?
Is if '' equal to null then why it doesn't work with where conditions?
please note: I have also tried >= <= instead of between and, but still it's seems to not work.

I hope my question is clear and I am not missing anything.

Answer 1

Because nvl('', to_date('01/01/1900', 'dd/mm/yyyy')) is automatically cast to varchar while nvl(null, to_date('01/01/1900', 'dd/mm/yyyy')) is date type.

You can check it using dump:

select dump(nvl('', to_date('01/01/1900', 'dd/mm/yyyy'))) from dual;

select dump(nvl(null, to_date('01/01/1900', 'dd/mm/yyyy'))) from dual;

Answer 2

Expanding on @PonderStibbon's explanation a bit, the documentation for the NVL() function says:

The arguments expr1 and expr2 can have any data type. If their data types are different, then Oracle Database implicitly converts one to the other.

In your first query the null isn't an identified data type, so the second argument can't be implicitly converted. In the second query the '' is equivalent to null but is still a string expression, so the date is implicitly converted to a string too, as dump shows.

When you compare your DateField column with the string value returned by nvl() the string is implicitly converted back to a date.

All those implicit conversions mean that your NLS settings come into play. With a common NLS_DATE_FORMAT setting like DD-MON-RR no date ever matches your condition, because of how RR is handled.

Consider:

select nvl('', to_date('01/01/1900', 'dd/mm/yyyy')),
  nvl('', to_date('31/12/2999', 'dd/mm/yyyy'))
from dual;

NVL('',TO NVL('',TO
--------- ---------
01-JAN-00 31-DEC-99

Remember these are strings, not dates. What happens if you use them in a comparison against a real date?

select * from dual
where date '2017-01-01' between nvl('', to_date('01/01/1900', 'dd/mm/yyyy'))
  and nvl('', to_date('31/12/2999', 'dd/mm/yyyy'));

which is really, because they evaluate to strings:

select * from dual
where date '2017-01-01' between '01-JAN-00' and '31-DEC-99';

but they those strings are implicitly converted back to dates:

select * from dual
where date '2017-01-01' between to_date('01-JAN-00') and to_date('31-DEC-99');

which uses the same NLS setting:

select * from dual
where date '2017-01-01' between to_date('01-JAN-00', 'DD-MON-RR')
  and to_date('31-DEC-99', 'DD-MON-RR');

which still looks like it should work, on the surface. But what are those dates now, really? Using your initial date and allowing implicit conversion until the last moment, when I want to see what's really in there:

select to_char(to_date(to_char(to_date('01/01/1900', 'dd/mm/yyyy'))), 'SYYYY-MM-DD'),
  to_char(to_date(to_char(to_date('31/12/2999', 'dd/mm/yyyy'))), 'SYYYY-MM-DD') from dual;

TO_CHAR(TO_ TO_CHAR(TO_
----------- -----------
 2000-01-01  1999-12-31

The RR rules mean that the two-digit year 00 is converted to 2000, while the two-digit year 99 is converted to 1999.

So your second query is actually doing:

select 1 from ordine where DateField between date '2000-01-01' and date '1999-12-31';

or

select 1 from ordine where date '2000-01-01' <= DateField
  and DateField <= date '1999-12-31';

and those conditions cannot both be true at the same time, so no date ever matches. Or to put it another way:

If expr3 < expr2, then the interval is empty

---

With a different NLS_DATE_FORMAT that preserved the full year your second query would also find the data you expect:

alter session set nls_date_format = 'DD-MON-RR';

select 1 from dual where sysdate between nvl('', to_date('01/01/1900', 'dd/mm/yyyy')) and nvl('', to_date('31/12/2999', 'dd/mm/yyyy'));

no rows selected

alter session set nls_date_format = 'SYYYY-MM-DD';

select 1 from dual where sysdate between nvl('', to_date('01/01/1900', 'dd/mm/yyyy')) and nvl('', to_date('31/12/2999', 'dd/mm/yyyy'));

         1
----------
         1

But don't rely on NLS settings or implicit conversions. Use null when you mean null, not an empty string, even if they are usually treated the same. (Oracle recommends that you do not treat empty strings the same as nulls.)

Answer 3

Oracle treats empty string as null, but only sometimes treats NULL as empty string.

select 'something' || '' 
from dual;

return 'something', but if the NULL wasn't treated as empty string, it would return NULL as the result would be unknown (which is expressed as NULL) .

Note that

select 1*NULL
from dual;

Returns NULL as expected.

So you just hit another case where this inconsistency appears. NVL considers '' a not null value (it is known, and it is empty string) and IS NULL operator in where condition does it the other way.That's Oracle for you...

---

edit: rewritten properly - sorry, long shift...

Answer 4

I think the question is not so interesting and it doesn't have short answer. It comes from having not enough knowledge. All will be clear if you read Oracle documentation about nulls, nulls and strings, nulls in functions and nulls in comparation.