Oracle and empty strings

Still as a reminder to myself in case some rows are missing from a query…

Oracle stores empty strings as NULL. So far I’m good, everybody’s aware of that, I think that I know everything. Then suddenly a query doesn’t return all the values I expect from it and I wonder why.

For example if I have the query

SELECT * FROM FOO WHERE BAR <> '';

I may expect it to return every row where BAR isn’t an empty string. Or in Oracle’s case a NULL. Well, too bad, because an empty string isn’t NULL. So any row where BAR is NULL will be ignored and won’t be in the resultset. I have to get rows like that with

SELECT * FROM FOO WHERE BAR IS NOT NULL;

But there is more. Let’s see the next query:

SELECT * FROM FOO WHERE BAR <> 'A';

What does this do? Gets every row where BAR doesn’t equal 'A'? Yes. Except the ones where BAR is NULL. And those rows will miss again.

SELECT * FROM FOO WHERE BAR <> 'A' OR BAR IS NULL;

is the way to go if we really want everything except where BAR is 'A'.

Moral of the story, I should read the documentation before jumping to conclusions, as it clearly states that “If you use any other condition with nulls and the result depends on the value of the null, then the result is UNKNOWN.” and also that “A condition that evaluates to UNKNOWN acts almost like FALSE. For example, a SELECT statement with a condition in the WHERE clause that evaluates to UNKNOWN returns no rows.”

(And of course it behaves the same with UPDATEs and not just with SELECTs.)