What You Should Know About NULL Values

Understand the Use of NULLs to Avoid Database Problems

Businesswoman at desk with computer
Tom Merton/OJO Images/Getty Images

Users new to the world of databases are often confused by a special value particular to the field — the NULL value. This value can be found in a field containing any type of data and has a very special meaning within the context of a relational database. It’s probably best to begin our discussion of NULL with a few words about what NULL is not:

  • NULL is not the number zero.
  • NULL is not the empty string (“”) value.

    Rather, NULL is the value used to represent an unknown piece of data. Often, database programmers will used the phrase "a NULL value", but this is incorrect. Remember: a NULL is an unknown value in which the field appears blank.

    NULL in the Real World

    Let’s take a look at a simple example: a table containing the inventory for a fruit stand. Suppose that our inventory contains 10 apples and three oranges. We also stock plums, but our inventory information is incomplete and we don’t know how many (if any) plums are in stock. Using the NULL value, we would have the inventory table shown in the table below.

    Fruit Stand Inventory

    InventoryIDItemQuantity
    1Apples10
    2Oranges3
    3PlumsNULL


    It would clearly be incorrect to include a quantity of 0 for the plums record, because that would imply that we had no plums in inventory. On the contrary, we might have some plums, but we’re just not sure.

    To NULL or NOT NULL?

    A table can be designed to either allow NULL values or not.

    Here is a SQL example that creates an Inventory table that allows some NULLs:

    SQL> CREATE TABLE INVENTORY(
    InventoryID INT NOT NULL,
    Item VARCHAR (20) NOT NULL,
    Quantity INT
    );

    The Inventory table here does not allow NULL values for the InventoryID and the Item columns, but does allow them for the Quantity column.

    While allowing a NULL value is perfectly fine, NULL values can cause problems because any comparison of values in which one is NULL always results in NULL.

    To check if your table contains NULL values, use the IS NULL or IS NOT NULL operator. Here is an example of IS NULL:

    SQL> SELECT INVENTORYID, ITEM, QUANTITY
    FROM INVENTORY
    WHERE QUANTITY IS NOT NULL;

    Given our example here, this would return:

    InventoryIDItemQuantity
    3Plums 

     

     

    Operating on NULLs

    Working with NULL values often produces NULL results, depending on the SQL operation. For example, assuming that A is NULL:

    Arithmetic Operators

    • A + B = NULL
    • A - B = NULL
    • A * B = NULL
    • A / B = NULL

    Comparison Operators

    • A = B = NULL
    • A != B = NULL
    •  
    • A > B = NULL
    • A !< B = NULL

    These are just some examples of operators that will always return NULL if one operand is NULL. Much more complex queries exist, and all are complicated by NULL values. The take home point is that, if you allow NULL values in your database, understand the implications and plan for them.

    That's NULL in a nutshell!

    Was this page helpful?