SQL NULL Value

NULL values are missing unknown data.

By default, columns in the table can store NULL values.

This chapter explains the IS NULL and IS NOT NULL operators.

SQL NULL Value

If a column in the 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.

The handling of NULL values is different 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.

SQL NULL Value Handling

Please see the following 'Persons' table:

Id LastName FirstName Address City
1 Adams John 　 London
2 Bush George Fifth Avenue New York
3 Carter Thomas 　 Beijing

Suppose the 'Address' column in the 'Persons' table is optional. This means that if a record without a value is inserted into the 'Address' column, the 'Address' column will use NULL to save the value.

So how do we test NULL values?

Comparison operators cannot be used to test NULL values, such as =, <, or <>.

We must use the IS NULL and IS NOT NULL operators.

SQL IS NULL

How do we select records that only have NULL values in the 'Address' column?

We must use the IS NULL operator:

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL

Result Set:

LastName FirstName Address
Adams John 　
Carter Thomas 　

Tip:Always use IS NULL to find NULL values.

SQL IS NOT NULL

How do we select records that do not have NULL values in the 'Address' column?

We must use the IS NOT NULL operator:

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL

Result Set:

LastName FirstName Address
Bush George Fifth Avenue

In the next section, we will learn about the ISNULL(), NVL(), IFNULL(), and COALESCE() functions.