Sql with empty string, same eligibility, different results

Question

I've read many things around how Oracle handles null or empty string.

Let's say I have a table with these datas :

id	myfield
id1	mydata1
id2	mydata2

select * from mytable where myfield <> ' ';
select * from mytable where myfield is not null;
select * from mytable where myfield <> '' ;

When executing the first two query, I get some results. Why not with the third one ?, knowing that : it's a NOT clause, and even though my empty string was reinterpreted as null, it would make it as a is not null clause (making the 2nd and third quite the same), making it eligible ?

score 2 · Accepted Answer · answered May 28 '21 at 13:50

Oracle is weird in terms of handling nulls. It was designed, developed, and tested in the 70s, well before the SQL Standard was officially established. Oracle is a great engine but it has quirks; this is one of them.

The expression:

where myfield <> ''

gets automatically rephrased by the Oracle engine as:

where myfield <> null

And, since no value can satisfy this predicate, no rows are returned.

See example at db<>fiddle.

Sql with empty string, same eligibility, different results

1 Answers1