In Oracle 8, there is no such thing as a zero-length string. Any zero-length string, either from a function call or the literal '', is treated as null.
Some of the consequences of this are counterintuitive. Here are some examples.
- The expression
X="is never true, regardless of whatXis. In particular: - The expression
''=''is not true. - If you store an empty string (
'') and read it back, the result will be null. - The expression
trim(' ')returns null.
Be careful of this when writing your queries, particularly if you are porting code between Oracle and another database.
It’s a really bad behavior
In MySQL empty string is not equal to null.
[...] McElwee blogged about this feature in more detail on his blog, but the jist of this nugget of goodness is that if you attempt to [...]
That really should be considered as a Oracle design bug. Oracle really is not that good in my opinion.
I am not at all an Oracle expert, but as a developer I have found Oracle databases to be annoying to work with. I think their selling point is the boring enterprise stuff like reliability, scalability, etc.
I personally like the fact that Oracle treats empty strings as null. Its a real pain having to test if something is null OR an empty string to find out if there is a usuable value there or not. And if I want to select rows where certain fields have blank values, I know that I will get everything I need by testing if null.
Programming without null is like Mathematics without zero.
I agree, ORACLE storing empty strings as NULL was a horrible design blunder. In a relational database, NULL is used to represent datum whose value is “unknown”. If I insert an empty string into a database field, I have specified a value for that field — a zero-length string. If I fetch that string from the database, the only correct result is an empty string.
Oops, correction:
A string variable that can’t be set empty is like a number variable that can’t be set zero.
bachew - I agree.
Also, you came back 20 days later to fix that. Awesome.
@Michael: if you don’t want to test for both ” and null, and your application treats both as semantically the same, just declare the field ‘not null’
[...] the semantic distinction between a value and not a value. Add the fact that the empty string doesn’t exist in Larry’s World to the unbelievably idiotic lack of a BOOLEAN column type and you begin to [...]
I agree that this is a horrible design decision. Not just because it screws compatibility with most other databases but because there is a school of thought held by many (the likes of C.J Date, and Hugh Darwin among them) that believe NULL is a Bad Idea and that there are better ways of representing UNKNOWN and NOT APPLICABLE (both of which people tend to use NULL for, often indistinguishably). This makes it almost impossible to excecise this discipline in Oracle.
As to the comment “Programming without null is like Mathematics without zero.” this is completely wrong-headed. A numeric 0 in a relational database is a 0 not a null. Tell me, what is x + y if x or y is NULL?
NULL complicates EVERYTHING and turns the clean 2 value logic of relational theory into the much more complex 3 value logic and this is the source of many problems.
Heres my two sense. Porting from SQL Server to Oracle (don’t ask why). In Sql server had:
select field1,field2 from table where field1 ”
This returned no results in Oracle, even though the majority of those fields had values. There was one that was null, which killed the query.
In Oracle I tried tiering it with
select field1,field2
from table
where trim(field1) ” and field1 is not null
which also died, no results. The solution was
select field1,field2
from table
where trim(field1) is not null
In my opinion, that is just poor db design. The majority of the rows had values, but oracle died on the one null. A large relational database should be able to handle a null value the same way it handles a regular value, but nulls always blow up on oracle.
that i need
@Bennett: if you call reliability and scalability boring enterprise stuff I guess you never had valuable data on a db
@Robert: NULLs don’t blow up on Oracle, it didn’t die on one NULL, it just considers ” as NULL, and an equality operator on a NULL is always false, it’s like saying WHERE col = NULL. That will always be false.
You can also try WHERE ” = ” OR NULL = NULL, you won’t have any results.
Anyway… I can’t think of a proper use of ” instead of NULL. I know it’s non standard but can you supply an example where this differentiation would be useful?
I guess most of its use comes from poorly sanitized input.
Cheers
@Kamal, I do think reliability and scalability are important. But they’re also boring to me. I’m glad that some people are interested in them though.
One of the more common uses of null is to indicate “unknown” as @Eric mentioned.
label='large'means that the label is “large”,label=''means the label is blank, andlabel=nullmeans that we don’t know. In Oracle you have to do this with an extra Boolean field.And let’s not forget that in Oracle,
I would love to see anybody try to argue that this isn’t nonsense.