SQL NULL Values
NULL values represent missing unknown data.
By default, a table column can hold NULL values.
This chapter explains the IS NULL and IS NOT NULL operators.
SQL NULL Values
If a column in a table is optional, we can insert a new record or update an existing record without adding a value to that column. This means that the field will be saved with a NULL value.
NULL values are handled differently from other values.
NULL is used as a placeholder for unknown or inapplicable values.
Note:NULL cannot be compared with 0; they are not equivalent.
Handling NULL Values in SQL
Look at the following "Persons" table:
| P_Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Hansen | Ola | Sandnes | |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
| 3 | Pettersen | Kari | Stavanger |
Suppose that the "Address" column in the "Persons" table is optional. This means that if you insert a record without a value for the "Address" column, the "Address" column will be saved with a NULL value.
So how do we test for NULL values?
It is not possible to test for NULL values using comparison operators, such as =, <, or <>.
We must use the IS NULL and IS NOT NULL operators.
SQL IS NULL
How do we select only the records that have a NULL value in the "Address" column?
We must use the IS NULL operator:
WHERE Address IS NULL
The result set looks like this:
| LastName | FirstName | Address |
|---|---|---|
| Hansen | Ola | |
| Pettersen | Kari |
Tip:Always use IS NULL to look for NULL values.
SQL IS NOT NULL
How do we select only the records that do not have a NULL value in the "Address" column?
We must use the IS NOT NULL operator:
WHERE Address IS NOT NULL
The result set looks like this:
| LastName | FirstName | Address |
|---|---|---|
| Svendson | Tove | Borgvn 23 |
In the next section, we learn about the ISNULL(), NVL(), IFNULL(), and COALESCE() functions.
Other Extensions