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;
AVG(A)
----------
1
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;
AVG(A)
----------
.5
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”).
