SQL NULL Values
What is a NULL Value?
A field with a NULL value is a field with no value.
If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.
Note: It is very important to understand that a NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation!
How to Test for NULL Values?
It is not possible to test for NULL values with comparison operators, such as =, <, or <>.
We will have to use the IS NULL and IS NOT NULL operators instead.
IS NULL Syntax
SELECT column_names
FROM table_name
WHERE column_name IS NULL;IS NOT NULL Syntax
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;Demo Database
Assume we have the following "Persons" table:
| ID | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Doe | John | 542 W. 27th Street | New York |
| 2 | Bloggs | Joe | London | |
| 3 | Roe | Jane | New York | |
| 4 | Smith | John | 110 Bishopsgate | London |
Suppose that the "Address" column in the "Persons" table is optional. If a record is inserted with no value for "Address", the "Address" column will be saved with a NULL value.
The IS NULL Operator
The following SQL statement uses the IS NULL operator to list all persons that have no address:
SELECT LastName, FirstName, Address FROM Persons
WHERE Address IS NULL;The result-set will look like this:
| LastName | FirstName | Address |
|---|---|---|
| Bloggs | Joe | |
| Roe | Jane |
Tip: Always use IS NULL to look for NULL values.
The IS NOT NULL Operator
The following SQL statement uses the IS NOT NULL operator to list all persons that do have an address:
SELECT LastName, FirstName, Address FROM Persons
WHERE Address IS NOT NULL;The result-set will look like this:
| LastName | FirstName | Address |
|---|---|---|
| Doe | John | 542 W. 27th Street |
| Smith | John | 110 Bishopsgate |
