Categories Software Related Stories SQL: The Universal Database Language -- 11/3/98 Next: The UPDATE statement

Continued from Comparisons and Logical Connectors

NULLs are a complex subject in SQL, and a detailed discourse on them is better suited to an advanced course in SQL than to a primer. But since they need special handling and you will probably encounter them, we'll talk about them briefly here.

First of all, when NULLs are tested in predicates, they require special syntax. If, for example, you wanted to view all the information on employees for which you were missing salary data, you could enter the following SELECT statement:

SELECT * FROM EMPLOYEES WHERE SALARY IS NULL;

Conversely, if you wanted to see all the information for every employee except those missing salary data, you could enter the following:

SELECT * FROM EMPLOYEES WHERE SALARY IS NOT NULL;

Notice that we used the keywords IS NULL or IS NOT NULL after the column name instead of the standard comparison forms: COLUMN = NULL or COLUMN <> NULL (or the logical operator NOT (NULL)).

That's pretty straightforward. It's when you're not explicitly testing for NULLs (and they're there) that things can get confusing.

For example, looking back at our EMPLOYEES table in Figure 1, you see that Indiana Jones is missing values for GRADE and SALARY. Both of these columns contain NULLs. So you would think that if you ran a query like

SELECT * FROM EMPLOYEES
   WHERE GRADE <= SALARY;

Indiana Jones would appear in the result table. Since NULLs should be equivalent, you'd expect them to pass the test of GRADE being less than or equal to SALARY. (It's a nonsensical query, but never mind that. SQL will let you do the comparison, since both columns are numbers.) However, Indiana Jones won't appear in the results of this query. Why?

As we mentioned earlier, a NULL represents a missing value (not, as one might think, a value of NULL). To SQL that means the value is unknown, and if a value is unknown, you can't compare it with any other value--even with another NULL. So SQL allows a third type of truth value in addition to true or false, namely the unknown value.

If either side of a comparison is NULL, the whole predicate is considered unknown. If an unknown predicate is negated or combined with another predicate using AND or OR, the result is unknown. Since result tables only comprise rows in which predicates return a value of true, NULLs would never pass the test. Hence the need for the special operators IS NULL and IS NOT NULL.

Next: The UPDATE statement

Published as PC Tech Feature in the 11/17/98 issue of PC Magazine.

Elsewhere on ZDNet
• Learn SQL Online -- ZD University
• Business Software: Databases -- ZD Products