Why does Oracle 9i treat an empty string as NULL?

Question

I know that it does consider ' ' as NULL, but that doesn't do much to tell me why this is the case. As I understand the SQL specifications, ' ' is not the same as NULL -- one is a valid datum, and the other is indicating the absence of that same information.

Feel free to speculate, but please indicate if that's the case. If there's anyone from Oracle who can comment on it, that'd be fantastic!

Answer 1

Indeed, I have had nothing but difficulties in dealing with Oracle, including invalid datetime values (cannot be printed, converted or anything, just looked at with the DUMP() function) which are allowed to be inserted into the database, apparently through some buggy version of the client as a binary column! So much for protecting database integrity!

Oracle handling of NULLs links:

http://digitalbush.com/2007/10/27/oracle-9i-null-behavior/

http://jeffkemponoracle.com/2006/02/empty-string-andor-null.html

Answer 2

According to official 11g docs

Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.

Possible reasons

1. val IS NOT NULL is more readable than val != ''
2. No need to check both conditions val != '' and val IS NOT NULL

Answer 3

I suspect this makes a lot more sense if you think of Oracle the way earlier developers probably did -- as a glorified backend for a data entry system. Every field in the database corresponded to a field in a form that a data entry operator saw on his screen. If the operator didn't type anything into a field, whether that's "birthdate" or "address" then the data for that field is "unknown". There's no way for an operator to indicate that someone's address is really an empty string, and that doesn't really make much sense anyways.

Answer 4

Because not treating it as NULL isn't particularly helpful, either.

If you make a mistake in this area on Oracle, you usually notice right away. In SQL server, however, it will appear to work, and the problem only appears when someone enters an empty string instead of NULL (perhaps from a .net client library, where null is different from "", but you usually treat them the same).

I'm not saying Oracle is right, but it seems to me that both ways are approximately equally bad.