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!

Feel free to speculate? Somehow I don't think that will provide you with the greatest set of answers.. — SCdF, Oct 15 '08 at 02:29
I suppose not, but I wasn't sure there'd be any certainty on the subject, so I figured I'd throw open the doors. Seems to have worked out okay, so far. — Chris R, Oct 15 '08 at 03:11
http://docs.oracle.com/database/121/SQLRF/sql_elements005.htm#SQLRF51094 — Jayan, Sep 16 '14 at 10:07

score 239 · Accepted Answer · edited Jun 11 '12 at 20:25

I believe the answer is that Oracle is very, very old.

Back in the olden days before there was a SQL standard, Oracle made the design decision that empty strings in VARCHAR/VARCHAR2 columns were NULL and that there was only one sense of NULL (there are relational theorists that would differentiate between data that has never been prompted for, data where the answer exists but is not known by the user, data where there is no answer, etc. all of which constitute some sense of NULL).

By the time that the SQL standard came around and agreed that NULL and the empty string were distinct entities, there were already Oracle users that had code that assumed the two were equivalent. So Oracle was basically left with the options of breaking existing code, violating the SQL standard, or introducing some sort of initialization parameter that would change the functionality of potentially large number of queries. Violating the SQL standard (IMHO) was the least disruptive of these three options.

Oracle has left open the possibility that the VARCHAR data type would change in a future release to adhere to the SQL standard (which is why everyone uses VARCHAR2 in Oracle since that data type's behavior is guaranteed to remain the same going forward).

score 63 · Answer 2 · answered Oct 15 '08 at 02:38

63

Tom Kyte VP of Oracle:

A ZERO length varchar is treated as NULL.

'' is not treated as NULL.

'' when assigned to a char(1) becomes ' ' (char types are blank padded strings).

'' when assigned to a varchar2(1) becomes '' which is a zero length string and a zero length string is NULL in Oracle (it is no long '')

answered Oct 15 '08 at 02:38

Brian

13,412
10
56
82

19

Wow, Tom's pretty snarky. Given that the questions are pertaining to an egregious divergence from SQL92, you'd think he'd be less punchy about it... although he might be tired of answering. – Chris R Oct 15 '08 at 03:10
8

The best thing about Tom is that you get a clear answer, which states **exactly** what he thinks. Look for some of the comments where people have used text speak on Ask Tom – Chris Gill Aug 27 '09 at 12:17
10

But it would be more precise if the second line was changed to *'' is not **always** treated as NULL.* – ypercubeᵀᴹ Jul 05 '11 at 07:48
3

@ypercube The quote does not get more precise by changing the word actually used by Tom. If you think Tom worded it confusingly, mmm. Maybe. I think he's _spot on_. The most confusing situations arise when `''` is being _implicitely_ converted to a VARCHAR2, **[such as `cast('' as char(1)) is null` which is... surprisingly TRUE](http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5984520277372#1368505100346370684)** – sehe Jul 19 '13 at 15:17
1

@sehe the confusing bit for me is select 1 from dual where ('' is null) – matt freake Aug 22 '13 at 15:58
I expect '' not to be treated as NULL [literal strings are chars no varchars](http://stackoverflow.com/a/22198336/2712726). Logic. But why then does the following query return zero rows? `select 1 as one from dual where 'a' > '' or 'a' < ''` – Patrick Fromberg Mar 28 '14 at 18:29
Because any comparision with a NULL value returns neither TRUE nor FALSE but UNKNOWN... and UNKNOWN OR UNKNOWN is UNKNOWN and will never justify a where-condition. You can easily check this, by surrounding your whole where-condition with NOT(...) and it will still yield 0 rows ;-) – Falco May 06 '14 at 15:24
1

@Falco the discussion is around '' being treated as a null. Not the behavior of a null value. – Brian May 06 '14 at 18:59
But if Patrick expects '' to be treated as NULL, why is he surprised it shows the SAME behaviour like an actual NULL value? The outcome of his line is the same whether he puts '' or NULL there – Falco May 07 '14 at 15:44
`'' is not treated as NULL.` Not correct, on my Oracle 11.2 the following code prints null: `if '' is null then SYS.DBMS_OUTPUT.PUT_LINE('null');end if;` – Roland May 27 '15 at 16:51
1

@Roland - in the context, he was saying `''` is not treated as null _in every situation_. The next line gave an example where assigning it to a `char` triggered its blank padding behaviour (although `char`s can also be null). I'm guessing the PL/SQL compiler is following `varchar2` semantics when it encounters an untyped `''`. – William Robertson Dec 07 '16 at 15:41

score 21 · Answer 3 · answered Oct 15 '08 at 13:46

Oracle documentation alerts developers to this problem, going back at least as far as version 7.

Oracle chose to represent NULLS by the "impossible value" technique. For example, a NULL in a numeric location will be stored as "minus zero", an impossible value. Any minus zeroes that result from computations will be converted to positive zero before being stored.

Oracle also chose, erroneously, to consider the VARCHAR string of length zero (the empty string) to be an impossible value, and a suitable choice for representing NULL. It turns out that the empty string is far from an impossible value. It's even the identity under the operation of string concatenation!

Oracle documentation warns database designers and developers that some future version of Oracle might break this association between the empty string and NULL, and break any code that depends on that association.

There are techniques to flag NULLS other than impossible values, but Oracle didn't use them.

(I'm using the word "location" above to mean the intersection of a row and a column.)

*Oracle documentation warns database designers and developers that some future version of Oracle might break this association between the empty string and NULL, and break any code that depends on that association* – could you please provide reference for this statement? — Piotr Dobrogost, Oct 15 '18 at 20:44
https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements005.htm — Walter Mitty, Oct 16 '18 at 00:29

score 19 · Answer 4 · answered Feb 18 '09 at 15:50

19

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.

answered Feb 18 '09 at 15:50

user67897

207
2
2

8

That only makes sense if you assume that every field in a data entry system is mandatory. A non-answer to a non-mandatory field (e.g. "Dog's Name") is valid, so an empty string still has a distinct purpose from NULL. Even with that assumption in place, I doubt early developers thought of Oracle as a "glorified backend for a data entry system" so I'm not sure this answer makes sense at all. – Jared Nov 13 '15 at 19:07

score 1 · Answer 5 · answered Mar 23 '15 at 11:34

1

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

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

answered Mar 23 '15 at 11:34

Sorter

9,704
6
64
74

8

In a fully ANSI-compliant database, you don't have to check for both conditions. `val <> ''` already excludes `NULL`. Perhaps you meant `val = '' OR val IS NULL`. But empty strings that don't compare as NULL are *useful*! – ErikE Feb 03 '16 at 16:02
I agree with the comparision part. – Sorter Jun 26 '19 at 05:44

score 0 · Answer 6 · answered Jun 05 '13 at 16:33

0

Empty string is the same as NULL simply because its the "lesser evil" when compared to the situation when the two (empty string and null) are not the same.

In languages where NULL and empty String are not the same, one has to always check both conditions.

answered Jun 05 '13 at 16:33

Alex Kreutznaer

1,170
8
18

1

Simply set `not null` constraint on your column and check only on empty string. – Egor Skriptunoff Jun 29 '13 at 23:27
8

Checking both conditions is trivial: `WHERE Field <> ''` returns true only if the field is not NULL and not empty, on databases with ANSI behavior for empty strings. – Jul 09 '14 at 14:24
2

By having them same you have a uniqueness problem because `NULL !== NULL`, but `'' === ''`, so I'd argue it's not the lesser evil. It's adding confusion. – Tomáš Fejfar Aug 09 '21 at 12:13

score -1 · Answer 7 · answered Aug 08 '15 at 15:11

Example from book

   set serveroutput on;   
    DECLARE
    empty_varchar2 VARCHAR2(10) := '';
    empty_char CHAR(10) := '';
    BEGIN
    IF empty_varchar2 IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('empty_varchar2 is NULL');
    END IF;


    IF '' IS NULL THEN
    DBMS_OUTPUT.PUT_LINE(''''' is NULL');
    END IF;

    IF empty_char IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('empty_char is NULL');
    ELSIF empty_char IS NOT NULL THEN
    DBMS_OUTPUT.PUT_LINE('empty_char is NOT NULL');
    END IF;

    END;

score -2 · Answer 8 · edited Jun 11 '12 at 20:27

-2

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.

edited Jun 11 '12 at 20:27

answered Apr 09 '10 at 19:52

erikkallen

33,800
13
85
120

3

Much, much more easier to debug. Also, if you see an empty cell or input on the screen, you know the data in the DB is null. In other DBs where ''<>NULL, you can't "see" if the data is null or '', this leads to very sneaky bugs. ''=null it's the sanest option, even if it is not standard. – Lucio M. Tato Oct 18 '13 at 20:02
5

“In other DBs where ''<>NULL, you can't "see" if the data is null or ''” => Usually, DB tools display NULLs differently from empty strings. Actually, even Oracle SQL Developer shows NULLs as “(null)”. I guess this is to distinguish NULL from whitespace but it is unrelated to the difference between NULL and empty strings. – Didier L Sep 23 '14 at 14:18

Cade Roux · Answer 9 · 2012-06-11T23:09:57.377

-6

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

edited Jun 11 '12 at 23:09

answered Oct 15 '08 at 02:27

Cade Roux

88,164
40
182
265

1

invalid datatime values? Not sure what that means. Have you posted this as a question here? – Oct 15 '08 at 15:17
1

The problem pre-dated stackoverflow - I got no useful information from Oracle forums and I created a workaround - I'll track my notes down and post here. – Cade Roux Oct 15 '08 at 15:56
Posted details as a question here. – Cade Roux Oct 15 '08 at 16:13

score -6 · Answer 10 · edited Jun 11 '12 at 23:43

First of all, null and null string were not always treated as the same by Oracle. A null string is, by definition, a string containing no characters. This is not at all the same as a null. NULL is, by definition, the absence of data.

Five or six years or so ago, null string was treated differently from null by Oracle. While, like null, null string was equal to everything and different from everything (which I think is fine for null, but totally WRONG for null string), at least length(null string) would return 0, as it should since null string is a string of zero length.

Currently in Oracle, length(null) returns null which I guess is O.K., but length(null string) also returns null which is totally WRONG.

I do not understand why they decided to start treating these 2 distinct "values" the same. They mean different things and the programmer should have the capability of acting on each in different ways. The fact that they have changed their methodology tells me that they really don't have a clue as to how these values should be treated.

Citation required for making a distinction between "null string" and NULL value. In any database except Oracle, a `VARCHAR` field can have a value (zero or more characters) or no value (NULL), full stop. — , Jul 09 '14 at 14:27
"Five or six years ago" from 2011 would fall in the 10g timeframe (10.1 released 2003, 10.2 in 2005). 10g absolutely did not introduce any global changes in the handling of nulls, and there has never been any distinction between `NULL` and a null valued string, and such a distinction make no sense. I'm afraid this answer is a complete fantasy. — William Robertson, Jul 21 '18 at 10:34

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

10 Answers10

Linked

Related