4

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

And now what I cannot understand: 

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.

Can someone explain it for me ?

Rafał Sokalski
  • 1,817
  • 2
  • 17
  • 29
  • 1
    Empty string are the same as nulls in Oracle. See [this question](https://stackoverflow.com/q/203493/266304), and [the docs](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Nulls.html), particularly the part about comparisons. – Alex Poole Jul 20 '18 at 12:37
  • 2
    [Posisble duplicate?](https://stackoverflow.com/q/7215202/266304) – Alex Poole Jul 20 '18 at 12:49
  • 1
    @AlexPoole Thanks for the links to the documents. Many people have opinions, facts are always welcome. The really curious thing is that, unlike much of Oracle's documentation, this one is clear! – Brian Leach Jul 20 '18 at 16:55

2 Answers2

5

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.

Captain Kenpachi
  • 6,960
  • 7
  • 47
  • 68
  • 1
    The **real** "gotcha" is that it's not _always_ treating empty strings as null. `'foo'||''` returns `'foo'`. But even more confusing is that `'foo'||null` **also** returns `'foo'` –  Jul 20 '18 at 12:41
  • @a_horse_with_no_name - but that *is* still treating the empty string the same as null? The confusion is that [the concatenation operator](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Concatenation-Operator.html) doesn't return null in both cases as you might expect from how other things behave, I think. (I hadn't noticed before that the docs say that might change one day... is that the same timescale as redefining `varchar`?) – Alex Poole Jul 20 '18 at 12:47
  • @AlexPoole: then the concatenation operator is not behaving according to the SQL standard. Because every expression involving `null` should yield `null` as in `42 * null` –  Jul 20 '18 at 12:53
  • @a_horse_with_no_name I came from Oracle to SQL Server, before Oracle had ANSI syntax. I was hopelessly confused that concatenating a null with a string in SQL Server resulted in null [grin]. Just the opposite of your experience. That was 15 minutes of debugging I wish I could get back :-) – Brian Leach Jul 20 '18 at 16:59
5

The reason is as @Captain Kenpachi explained. If want to compare two strings (or other types that are the same) and want to be tolerant of NULLs (or empty string in Oracle as it treats it as the same) then you need to involve an IS test.

You could try the common cheat of using a rogue value that will never be used but Murphy's Law dictates that one day someone will. This technique also has the drawback that the rogue value should match the type of the thing you are comparing i.e. comparing strings you need a rogue string while comparing dates you need a rouge date. This also means you can't cut-and-paste it liberally without applying a little thought. Example: WHERE NVL(col1,'MyRougeValue')=NVL(col2,'MyRougeValue')

The standard version is to explicitly test for NULLs WHERE (col1=col2 OR (col1 IS NULL AND col2 IS NULL))

The opposite becomes WHERE NOT(col1=col2 OR (col1 IS NULL AND col2 IS NULL))

I have seen the a long winded opposite version (as seen in Toad's data compare tool) WHERE (col1<>col2 OR (col1 IS NULL AND col2 IS NOT NULL) OR (col1 IS NOT NULL AND col2 IS NULL))

Oracle does have a handy DECODE function that is basically is IF a IS b THEN c ELSE d so equality is WHERE DECODE(col1,col2,1,0)=1 and the opposite is WHERE DECODE(col1,col2,1,0)=0. You may find this a little slower than the explicit IS test. It is proprietary to Oracle but helps make up for the empty string problem.

Unoembre
  • 535
  • 2
  • 9