Does Oracle Treat Empty Strings as NULL?

Oracle’s unique handling of empty strings: NULL or not-NULL?

Oracle 9i's practice of equating the empty string ('' ) with NULL has been confusing to many developers. Why would a data type designed to store characters equate a valid string to missing data?

To understand this peculiarity, we must delve into the history of Oracle. Before the advent of the SQL standard, Oracle defined VARCHAR/VARCHAR2 columns to treat empty strings as NULL values. This design stems from Oracle's unique interpretation of NULL, which covers all forms of missing data.

When the SQL standard came along and established the difference between NULL and the empty string, Oracle faced a dilemma. Changing the behavior of an existing VARCHAR column will break old code, and violating the standard will deviate from industry practice. To minimize disruption, Oracle made the controversial decision to retain the original definition.

Oracle acknowledges that this practice deviates from the standard, but leaves room for possible future changes to align with the standard. Therefore, developers would be wise to prefer VARCHAR2 over VARCHAR for future compatibility of their code. There has been much speculation as to why Oracle maintains this unusual behavior, but the reason remains an official mystery, perhaps rooted in the need to maintain backwards compatibility in an ever-changing standards landscape.

The above is the detailed content of Does Oracle Treat Empty Strings as NULL?. For more information, please follow other related articles on the PHP Chinese website!