MySQL NULL Values
What is a NULL Value?
A field that has the value NULL is one that is empty.
When a field in a table is optional, it means that you can change or create a new record without filling it up. After that, a NULL value will be recorded in the field.
Note: A field containing spaces or a zero value are not the same as a NULL value. When a field is left blank during record creation, it has a NULL value!
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
Below is a selection from the “Customers” table in 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
To check for empty values (also known as NULL values), utilize the IS NULL operator.
All of the customers with a NULL value in the “Address” field are listed using the SQL below:
Example
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NULL;
Advice: When searching for NULL values, always use IS NULL.
The IS NOT NULL Operator
To check for non-empty values (NOT NULL values), use the IS NOT NULL operator.
All of the clients with a value in the “Address” column are listed using the SQL below:
Example
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NOT NULL;
