This chapter is from the book
This chapter is from the book
This chapter is from the book
Boolean Logic and NULL Values
At the beginning of this chapter, we stated that the Boolean logic in SQL evaluates complex expressions as either true or false. This assertion was not completely accurate. When evaluating the conditions in a WHERE clause, there are actually three possibilities: true, false, and unknown. The possibility of an unknown value derives from the fact that columns in SQL databases are sometimes allowed to have a NULL value. As mentioned in Chapter 1, NULL values are those for which there is an absence of data.
SQL provides a special keyword to test for the presence of NULL values for a column specified in a WHERE clause. The keyword is IS NULL. This is similar in purpose to the ISNULL function seen previously in Chapter 4. Let’s look at an example taken from this Products table:
ProductID |
Description |
Inventory |
1 |
Printer A |
NULL |
2 |
Printer B |
0 |
3 |
Monitor C |
7 |
4 |
Laptop D |
11 |
This table indicates how many units of each product are currently in inventory. For this example, we’ll imagine that as rows are added to the Products table, they are initially not given an inventory value. As a result, the Inventory column displays a value of NULL until someone later counts the items in stock and enters an inventory amount. In this example, we see that there’s a positive inventory for Monitor C and Laptop D but nothing in stock for Printer B. The inventory for Printer A is indeterminate.
Let’s say that we attempt to use the following SELECT to find products that have nothing in inventory:
SELECT Description, Inventory FROM Products WHERE Inventory = 0
This would return:
Description |
Inventory |
Printer B |
0 |
This is not quite what we wanted. The statement correctly selects Printer B since it has an inventory amount of 0. However, we would have also liked to see Printer A, since its inventory amount is not known. To correct this, we need to issue:
SELECT Description, Inventory FROM Products WHERE Inventory = 0 OR Inventory IS NULL
This returns:
Description |
Inventory |
Printer A |
NULL |
Printer B |
0 |
We now see Printers A and B. Note that the IS NULL keyword can also be negated as IS NOT NULL, which allows for the retrieval of rows that do not have NULL for the specified column. We’ll additionally mention that the ISNULL function, discussed in Chapter 4, can provide an alternative to the IS NULL keyword. The equivalent of the previous SELECT statement, using the ISNULL function, is:
SELECT Description, Inventory FROM Products WHERE ISNULL(Inventory, 0) = 0
This SELECT retrieves the same two rows. The ISNULL function converts all values for the Inventory column with a value of NULL to 0. This produces the same result as the previous statement, which tested for a value of 0 or NULL.