For some reason the ability to handle NULLS in SQL
statements can confuse some. This article takes a look at how to think of NULLs.
For some reason the value of NULL has confused
many and even started feuds across the internet for the slightest slip of the
tongue. Hopefully I will not slip myself here.
I often wonder why there is such confusion
over such a simplistic concept. I think the confusion stems from the fact that,
like one of my early professors stated, statements about the supossed value of
NULL have stuck in our brains. My professor made a statement something like
this: “NULL has a value, we just don’t know what it is“. I personally think
that because people place the term “value“ around the word NULL that it for
some reason is given some form of literal value. After all we can use columns
that contain NULL in conditional comparisons. Simply stated, a column that
contains a NULL is said to have no value or is unknown. I personally think that
we should limit ourselves from using any reference to a “value“ when talking
about NULL. So instead of saying “it contains a NULL value“ we should just say
“it contains a NULL“.
For simplicities sake, we should first talk about how NULLs
get into a column. There are basically two ways a NULL can get into a column of
a table. I will just focus on the INSERT statement here for simplicity.
Assume we have a table called TABLE_NULL with the following
columns.
CREATE TABLE table_null
(id NUMBER NOT NULL,
name VARCHAR2(10));
We can then INSERT into the table by explicitly stating the
NULL keyword.
SQL> INSERT INTO table_null values (1, NULL);
Alternatively, we can imply a NULL by not specifying the
NULLABLE fields. Notice that you must specify the required columns in the
insert list.
SQL> INSERT INTO table_null (ID) values (2);
Warning: Oracle allows for the insertion of a
NULL through the use of ‘’ as an empty value. This can be done for character
and numeric fields alike.
SQL> INSERTINTO table_null values (1, ‘’);
Even though I know this is quite a wide practice of doing so,
this should be avoided at all costs, as Oracle has stated they may change this
“feature”. In addition, as will be shown later in this article, it might
produce a false sense of value for the NULLs entered.
Getting a NULL into a column is not typically the hard part
of dealing with NULLS. It is the extraction of information in a table that gets
a bit confusing. For this part of the article, I will focus on the following
two rows of data that is in our TABLE_NULL table. (If there is an absence of
data that means it contains a NULL).
ID NAME
———- ———-
1 1
2
When comparing a column that contains a NULL you must use
either ‘IS NULL’ or ‘IS NOT NULL’. Remembering that an evaluation of a
condition is either TRUE or FALSE, anything other than using ‘IS NULL’ or ‘IS
NOT NULL’ against a NULL would result to something unknown (not TRUE or FALSE).
A few quick examples help here. Notice that the use of equality ‘=’, while it
may be experienced as an evaluation of FALSE, actually is an evaluation of
UNKNOWN and returns nothing.
SQL> SELECT * FROM table_null WHERE name IS NULL;
ID NAME
———- ———-
2
1 row selected.
SQL> SELECT * FROM table_null WHERE name IS NOT NULL;
ID NAME
———- ———-
1 1
1 row selected.
SQL> SELECT * FROM table_null WHERE name = NULL;
no rows selected
The evaluation of an SQL statement to UNKNOWN is a key
principle when writing and interpreting the results of a SQL statement. There
is a big difference here as it is really saying that Oracle cannot evaluate the
condition and thus does not return a row. Where our minds might think NULL =
NULL it is not the case. Really we should be saying to ourselves does
“something unknown” = “something unknown”. In which case our logic and outcome
would more closely resemble what the result set would be.
This UNKOWN can cause havoc on some programming out there. A
long time ago, when counting rows in a table that met some condition, we were
told not to use the ‘*’ in the counting of rows in a SQL statement. For instance,
we would get the following result for our demo data.
SQL> SELECT COUNT(*) FROM table_null;
COUNT(*)
———-
2
1 row selected.
Over time we were told to replace the ‘*’ in the COUNT
function with a column in the table. This was to speed up some performance
problems in the Oracle engine. So if we replaced the ‘*’ with the column ID we
would get the following.
SQL> SELECT COUNT(id) FROM table_null;
COUNT(ID)
———-
2
1 row selected.
Now suppose we happen to choose a column that was NULLABLE. Or
let’s say that over time the name column, that once was a NOT NULL column,
became a NULLABLE column. The following SQL statement would have the following
results. You can see that there could be a huge problem with any subsequent
processing of an application. The reason this happens is that an UNKNOWN in a
function evaluates to an UNKNOWN and thus is unable to be counted, or used by
any function for that matter.
SQL> SELECT COUNT(name) FROM table_null;
COUNT(NAME)
———–
1
1 row selected.
One way to remedy some of the hassles of using NULLs is to
wrap columns of concern with other functions such as DECODE or NVL. When doing
this it gives the coder the option to replace NULL values with something more
acceptable to the SQL being performed. For instance, the previous COUNTing
example could be helped along to “possibly” get the required results by the
following SQL.
SQL> SELECT COUNT(NVL(name,0)) FROM table_null;
COUNT(NVL(NAME,0))
——————
2
1 row selected.
While databases, tables, and columns were intended to store
values there will no doubt be times when we will have to store something quite
different from a value. Is a nullable column a placeholder for future values or
is it something quite different? Remember that NULL isn’t something more or
less than a value, it isn’t an empty string, it isn’t zero, but is actually just
something we don’t know.
