THE WORLD'S LARGEST WEB DEVELOPER SITE

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