Contents

Why is an empty string in Oracle NULL

In relational database theory we distinguish a entity that is unknown, which is indicated as NULL or a value 0 or and empty string. Back in the days before the SQL-92 standard, Oracle made the design decision that empty strings in VARCHAR columns were threaded as NULL. When the SQL standard came it was defined that NULL and the empty string were distinct entities. But Oracle developers already had code that assumed the two were equivalent so they kept the VARCHAR comparison semantics and introduced the VARCHAR2 witch guaranteed comparison semantics for all future.
Oracle has left open the possibility that the VARCHAR data type would change in a future release to adhere to the SQL standard . Therefore everyone uses VARCHAR2 in Oracle because that data type's behavior is guaranteed in the future while VARCHAR's behavior may change.

Background on CHAR and VARCHAR Strings in SQL Standards

The SQL Standard ANSI X3.135-1986 was not very specific about empty stings. Up to Oracle RDBMS v6, which was introduced in 1988, the design decision has been made that an empty string is treaded as null as it seems that an empty string has no value. However this is clearly against the relational database model which distinguished unknown null value from empty. The ANSI SQL-92 Standard introduced new data types in July 1992. One of them was VARCHAR and it also clearly distinguishes NULL and empty strings. Oracle was now in violation of the SQL92 Standard.

CHAR and VARCHAR versus VARCHAR2

In Version 6 of the Oracle RDBMS a CHAR variable length character strings max length 255 char that compares NON-PADDED CHAR Values. VARCHAR was a synonym to CHAR.
In Version 7 of the Oracle RDBMS you had three choices:
CHAR
Now FIXED length strings of max 255 char that Compares BLANK-PADDED CHAR values.
VARCHAR
Variable length char. strings of max length 2000 characters that compares NON-PADDED CHAR Values with the established '' = NULL comparison semantics
VARCHAR2
the newly introduced type is the same as VARCHAR except that the comparison semantics is guarantied for all future releases. this was recommended so to be compatible with exiting software.


NOTE: Oracle initially recommended fixed length CHAR for performance when it was introduced with Oracle Version 7. This way a deleted fixed length row can be replaced by a new one to avid fragmentation. This is obviously not true for variable length strings.

Status: Published Date: 2014-10-15 00:23:19 +0200 (Mi, 15 Okt 2014) Revision: 27

Copyright bei Andreas Haack (C) 2014. Diese Seite wird so wie sie ist zur Verfuegung gestellt, ohne irgenweche Garantien der Verwendbarkeit fuer bestimte Zwecke. Die auf dieser Seiten angebrachten Links liegen ausserhalb der redaktionellen Verantwortung von Andreas Haack und es wird keine Haftung oder Garantie uebernommen. Die Seiten sind Copyright (c) 2014 von Andreas Haack. Kein Teil darf ohne die schriftliche Einverstaendnis von Andreas Haack veroeffentlicht werden.
The page is provided 'as is' , without warranty of any kind, express or implied, including but not limited to the warranties of merchantability, fit- ness for a particular purpose and non-infringement. In no event shall Andreas Haack be liable for any claim, damages or other liability. This page is copyrighted property of Andreas Haack. Copyright by Andreas Haack (c) 2014 . No part of this page may be published without written permission for Andreas Haack. A hyper-link may created to this page but NOT to the embedded elements of this page. It may be freely downloaded for private purpose only as long as it is unaltered.