MySQL NULL Values

What is a NULL Value?

A field with a NULL value indicates that the field does not currently have a value assigned to it.

When a field in a table is optional, you can insert or update a record without specifying a value for this field. As a result, the field will be stored with a NULL value.

Note: A NULL value differs from a zero value or a field filled with spaces. A NULL value indicates that the field was intentionally left blank during record creation.

How to Test for NULL Values?

To test for NULL values, we must use the IS NULL and IS NOT NULL operators instead of comparison operators like =, <, or <> in SQL queries.

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

Here is a portion of the “Customers” table from the Northwind sample database:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds FutterKiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
5	Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden

The IS NULL Operator

The IS NULL operator checks for empty values (NULL values).

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

Example

SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NULL;

Tip: Always use IS NULL to search for NULL values in SQL queries.

The IS NOT NULL Operator

The IS NOT NULL operator checks for non-empty values (NOT NULL values).

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

Example

SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NOT NULL;