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_namesFROM table_name
WHERE column_name IS NULL;

IS NOT NULL Syntax

SELECT column_namesFROM 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