sql_null_values

SQL NULL Values

NULL values represent missing, unknown, or inapplicable data in a database column.

By default, a table column can hold NULL values unless a NOT NULL constraint is specified.

This chapter explains how to correctly test for NULL values using the IS NULL and IS NOT NULL operators.

Understanding SQL NULL Values

If a column in a table is optional (i.e., does not have a NOT NULL constraint), you can insert a new record or update an existing record without providing a value for this column. In such cases, the database stores a NULL value in that field.

A NULL value is different from an empty string (”) or the number zero (0). It is a special marker indicating that data does not exist or is unknown.

Treating NULL values requires special consideration because standard comparison operators (like =, <>, <, >) do not work as expected with NULL. For instance, column = NULL will not return rows where column is NULL; it will actually return unknown (which is treated as false in WHERE clauses).

	Important: NULL is not equivalent to 0 or an empty string. Comparing NULL to any value (even another NULL) using standard comparison operators yields UNKNOWN, not TRUE or FALSE.

Working with NULL Values in SQL

Consider the following “Employees” table. The “Address” column is optional and can contain NULL values:

EmployeeID
LastName
FirstName
Address
City


1
Hansen
Ola
NULL
Sandnes


2
Svendson
Tove
Borgvn 23
Sandnes


3
Pettersen
Kari
NULL
Stavanger

4
Jensen
John
Aker Brygge 5
Oslo

If we insert a record for a new employee without specifying their address, the “Address” field for that employee will be NULL.

How do we filter records based on NULL values?

As mentioned, standard comparison operators like =, <, or <> cannot be used to test for NULL values directly. Instead, SQL provides the IS NULL and IS NOT NULL operators.

SQL IS NULL Operator

To select records where the “Address” column is NULL, you use the IS NULL operator.

Example:

SELECT LastName, FirstName, Address
FROM Employees
WHERE Address IS NULL;

The result-set will include employees whose address is not known:

LastName
FirstName
Address


Hansen
Ola
NULL


Pettersen
Kari
NULL

	Tip: Always use IS NULL to check for the presence of NULL values. Never use column_name = NULL.

SQL IS NOT NULL Operator

To select records where the “Address” column has a known value (i.e., not NULL), you use the IS NOT NULL operator.

Example:

SELECT LastName, FirstName, Address
FROM Employees
WHERE Address IS NOT NULL;

The result-set will include employees with a recorded address:

LastName
FirstName
Address


Svendson
Tove
Borgvn 23

Jensen
John
Aker Brygge 5

Additional Notes on NULL:

Aggregate functions like COUNT(column_name), SUM(), AVG(), MIN(), MAX() typically ignore NULL values. COUNT(*) is an exception; it counts all rows regardless of NULLs in specific columns.

Functions like COALESCE() or database-specific functions such as IFNULL() (MySQL) or ISNULL() (SQL Server) can be used to substitute NULL values with a default value in query results. These are covered in a subsequent chapter.