Larry Steinle

SQL Standards dictate a very unique behavior for null values. You would think these behaviors would be consistent across databases. This article is for you Microsoft SQL developers who’ve been tossed into the Oracle world and are confused by the appalling violation of null behavior.

Introduction to Null

In SQL the null value is used to optimize data storage and indicate the lack of a value. Think of it as the lack of a decision on what to store. Make sense so far?

This is where beginner programmers run into problems. Because null is the lack of a value it means you can’t compare it to any value…not even itself. Null never equals null and null never not equals null.

Working with Null

As such there are special operators to help the developer work with null.

In the following query the IS keyword is used to return all records where colA is Null; lacking any value assignment:
SELECT * FROM table WHERE colA IS NULL

This next sample returns all records where colA is not Null; containing a value assignment:
SELECT * FROM table WHERE colA IS NOT NULL

The next sample demonstrates how to get all records that match a value or have no value:
SELECT * FROM table WHERE colA IS NULL OR colA = 10

The same behavior can be accomplished using Oracle’s equivalent of ISNULL, the NVL command:
SELECT * FROM table WHERE NVL(colA, 10) = 10

Finally, the same effect using Oracle DECODE command which is a simplified type of CASE statement in Oracle. Basically the DECODE command states that when the value from the first argument matches the second argument return the third argument else return the fourth argument. More can be done with DECODE but for now let’s stick with a simple example:
SELECT * FROM table WHERE DECODE(colA, NULL, 10, colA) = 10

If you are concerned with query optimization you should avoid the use of NVL in the where clause. DECODE is faster than NVL and the standard OR syntax is faster than DECODE. What I fail to understand is why both the NVL and CASE statement syntax is so much slower than DECODE. I can only assume it is a legacy engine optimization issue as DECODE existed before NVL and CASE in the Oracle world.

Enters the Villain

As explained earlier a Null value indicates the lack of a value. According to SQL Standards an empty string is a value and is not null. But that’s probably why you are reading my boring article. You fail to understand why you get all records that have null and an empty string when you only want null rows.

Oracle world violates SQL standards by automatically converting an empty string to null. So if you are trying to find all rows that have an empty string you will get nothing.

These next two statements are the same:
SELECT * FROM table WHERE colA = '';
SELECT * FROM table WHERE colA = NULL;

Since null never equals anything including itself and since an empty string in Oracle is actually Null no records will be matched and our query will return an empty recordset.

Unbelievable!

Looking for an Empty String

So how do you find an empty string in Oracle? With a little extra effort!

The LENGTH operator returns the number of characters in a string value. An empty string returns 0 characters. The length of Null is…wait for it…Null!

When looking for records with an empty string you must use the LENGTH function!
SELECT * FROM table WHERE LENGTH(colA) = 0;

Summary

So now we understand that in most (but not all) cases Oracle treats an empty string as a Null value. Failing to understand this distinction in the Oracle databases can cause confusing and seemingly misleading errors in your code. As such these rules also apply to PL code in if…then statements and value assignments. When working with nulls and empty strings in Oracle take a moment to consider the effects and ensure you’ve properly tested for null, empty string and strings with values.

For additional information on the null and empty string behaviors refer to Oracle documentation on VARCHAR2 data types, and the NVL, CASE, DECODE, IS and NOT operators.

This article is dedicated to my good friend, Colin.

Happy coding!