Understanding the Value of NULL

When a DBMS finds a NULL value in a column, it interprets it as undefined or unavailable. The SQL-92 standard specifies that a DBMS cannot assign or assume an explicit or implicit value to a NULL column.

A NULL is not the same as a space (in a character column), a zero (in a numeric column), or a NULL ASCII character (which is all zeroes) (in a character column). In fact, if you execute the SQL statement

 SELECT * FROM customer WHERE education = NULL

the DBMS will not display any rows, even if the education column in some of the rows in the CUSTOMER table has a NULL value. According to the SQL standard, the DBMS cannot make any assumption about a NULL value in a column-it cannot even assume that a NULL value equals NULL!

There are several reasons that a column may be NULL, including:

• Its value is not yet known. If your STUDENT table includes a RANK_IN_CLASS column, you would set its value to NULL on the first day of school.

• Its value does not yet exist. If your MARKETING_REP table includes an APPOINTMENT_QUOTA, the column's value would be NULL until set by the marketing room manager after the marketing rep completes his or her training.

• The column is not applicable to the table row. If your EMPLOYEE table includes a MANAGER-ID column, you would set the column to NULL for the company owner's row.

Be selective about the columns in which you allow the DBMS to store NULL values. A PRIMARY KEY column (which you will learn about in Tip 172, "Using the PRIMARY KEY Column Constraint to Uniquely Identify Rows in a Table"), cannot have a NULL in any of its rows. After all, a PRIMARY KEY column must be unique in each and every row. Since the DBMS cannot make any assumptions about the value of a NULL, it cannot say with certainty that the NULL value in one row would be the same as the value in another row once the column's value is no longer unknown (or becomes defined).

Also, if you plan to use a column in functions such as MIN, MAX, SUM, AVG, and so on, be sure to apply the NOT NULL constraint (which you will learn about in Tip 191, "Using the NOT NULL Column Constraint to Prevent NULL Values in a Column") to the column. If you use one of the aggregate functions on a column that has a NULL in a row, the result of the function will be indeterminate (that is, NULL). After all, the DBMS cannot compute the SUM of the values in a column if there are one or more rows in the table whose column value is unknown.

In summary, think of NULL as an indicator rather than a value. When the DBMS finds a NULL in a column of a row in a table, the DBMS "knows" that data is missing or not applicable.