SQL Basics

SQL Functions

SQL Joins

SQL Advanced

SQL Database

SQL Tables

SQL Constraints

SQL Performance

SQL Data Types

SQL Security

SQL NULL Values

❮ Previous Next ❯

SQL NULL Values

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.

Important: 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.

Syntax

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

Below is a selection from the Customers table. Note that the Address for customer 2 is NULL.

Customers Table:

CustomerID CustomerName Address
1 Alfreds Futterkiste Obere Str. 57
2 Ana Trujillo Emparedados NULL
3 Antonio Moreno Taquería Mataderos 2312

Example 1: The IS NULL Operator

The IS NULL operator is used to test for empty values (NULL values).

The following SQL lists all customers with a NULL value in the "Address" field:

IS NULL Example

SELECT CustomerName, Address FROM Customers
WHERE Address IS NULL;

Example 2: The IS NOT NULL Operator

The IS NOT NULL operator is used to test for non-empty values.

The following SQL lists all customers with a value in the "Address" field:

IS NOT NULL Example

SELECT CustomerName, Address FROM Customers
WHERE Address IS NOT NULL;
❮ Previous Next ❯