Tutorials

Handling NULLS in SQL statements

By James Koopmann

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.