NULL is not zero!

Some time ago I wrote a post about how COUNT(*) and COUNT(column) are semantically different things (link). Such queries may return different results if the column counted has NULLs in it. And the difference comes from that NULL is not a value, it’s rather a state which says “value unknown” or “no value entered”.

So, you better understand how NULLs interact with your SQL constructs if you call yourself a DBA or a database developer ;-)

Here’s another example about how misunderstanding NULLs may cause your application to return different results than what was intended.

I will create a little table with TWO rows in it:

SQL> create table t(a int);
Table created.

SQL> insert into t values(1);

1 row created.

SQL> insert into t values(null);

1 row created.

SQL> select avg(a) from t;


When I take an average of the 2 values in these rows I get average of 1.

Now lets update the NULL (no value) to 0 (an actual value of zero).

SQL> update t set a=0 where a is null;

1 row updated.

SQL> select avg(a) from t;


As you see, as we now have an actual value in the other row (as opposed to “no value”), the AVG function takes that zero into account.

Hopefully this illustrates once more that NULL does not mean zero or any other value, it means NO value. If you do aggregation functions (count,avg) over NULLs then you must understand that Oracle treats NULLs as no value and doesn’t account these “no values”, thus your queries may behave differently than what your intuition might say (and yes its always good to read documentation about what exactly a given SQL construction/function does in the given database engine instead of relying on “common sense”).

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

This entry was posted in Oracle and tagged , . Bookmark the permalink.

8 Responses to NULL is not zero!

  1. Ilmar Kerm says:

    Yes, in Estonia this topic needs a special attention in SQL courses :)

  2. Tanel Poder says:

    I’d say everywhere in the world!

  3. Tanel Poder says:

    …the important part is to make an informed decision, whether a value should be set to 0 or left NULL in case the value is “missing”. And this comes directly from business requirements, it’s not really a technical problem as such…

  4. Joel Slowik says:

    The nvl() function is handy in this case to define what a null could mean in an aggregate function. I use the nvl() function all the time to prevent misinterpretation of null.

  5. Louis Frolio says:

    Having been in this business for 13 years I am still shocked to find folks who don’t understand what “null” really means. In addition, as illustrated here how you form your query will have an impact on how nulls are handled. I have found (working with many software engineering teams) that in some scenarios it is much more efficient to persist values to represent nulls and then searching on that value to find the nulls. Think about what Oracle’s query optimizer does with a query where “.. table.column is null ..” You are looking for nothing and there can be cases where a full table scan might be implemented.

  6. Hans Forbrich says:

    Oracle made a decision years ago to equate null to ‘empty string’. I believe that has had an influence on the challenge we face with NULL in general.

    The habit of defining a default value for ‘unknown’ has been ingrained in IT for years. Using the reverse logic, it is easy to see why people jump to the conclusion that null might be a specific value.

    You provide a great and simple example to present to students.

  7. Pingback: Log Buffer #174: a Carnival of the Vanities for DBAs | The Pythian Blog

  8. Ericcn says:

    Oh, yes. Null value wouldn’t be counted in for the grouping function, like avg().

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>