Comparing two empty Strings in Oracle SQL

Hi today I have met with weird situation. I had a where clause where was condition which returns String and I wanted to check if it’s empty or not. And when it returns empty string Oracle still treat it like a different Strings. So I went further and prepared simple queries:

select 1 from dual where 1 = 1;
returns: 1

select 1 from dual where 'A' = 'A';
returns: 1

​x
 
select 1 from dual where 1 = 1;returns: 1​select 1 from dual where 'A' = 'A';returns: 1​

And now what I cannot understand:

select 1 from dual where '' = '';
No result.

 
select 1 from dual where '' = '';No result.​

Even if I check if they are different there is still no result.

select 1 from dual where '' != '';
No result.

 
select 1 from dual where '' != '';No result.​

Can someone explain it for me ?

Answer

Oracle treats empty strings as NULL. It’s a gotcha. Make a note of it and hope it never bites you in the butt in production.

Advertisement

Answer