SQL NULL values - IS NULL and IS NOT NULL
NULL values represent missing unknown data.
Column default, the table can be stored NULL values.
This chapter explains the IS NULL and IS NOT NULL operators.
SQL NULL values
If a column in the table is optional, then we can insert new records or update existing records without adding value to the column. This means that the field will be NULL values are saved.
Handling of NULL values and other values are different.
NULL is used as unknown or not applicable placeholder values.
Note: You can not compare NULL and 0; that they are not equivalent.
NULL values in SQL processing
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 |
If the "Persons" table in the "Address" column is optional. This means that if a column insert a record with no values in the "Address", "Address" column will be saved with a NULL value.
So how do we test for NULL values?
Unable to use a comparison operator to test the NULL value, such as =, <, or <>.
We must use the IS NULL and IS NOT NULL operators.
SQL IS NULL
How do we select only the records with NULL values in the "Address" column in it?
We must use the IS NULL operator:
WHERE Address IS NULL
The result set as follows:
| 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 "Address" column record a NULL value without it?
We must use the IS NOT NULL operator:
WHERE Address IS NOT NULL
The result set as follows:
| LastName | FirstName | Address |
|---|---|---|
| Svendson | Tove | Borgvn 23 |
In the next section, we understand the ISNULL (), NVL (), IFNULL () and COALESCE () function.