In this article, we’re going to see how to test null values in an SQL database.
1. What is a NULL Value?
Basically, a field with a NULL value in a SQL table is a field without value. When creating a table, we can mark a column that accepts null values when inserting data into the table.
A null value is different from a zero value or a field that contains only spaces (a.k.a. blank field). Further, we can put or not a value for that during the record creation in the table.
2. How to Test for NULL Values?
We can’t use the SQL comparison operators such as = (equal), < (less than), >(greater than) or <> (not equal).
To test NULL values in SQL, we use the IS NULL and IS NOT NULL operators. The use of these operators is pretty simple as we see below:
IS NULL operator syntax
SELECT column_names FROM table_name WHERE column_name IS NULL;
IS NOT NULL operator syntax
SELECT column_names FROM table_name WHERE column_name IS NOT NULL;
3. The IS NULL Operator
Let’s do a practical exercise. Below, I created a simple table called “Customers” and added some data to it.
| CustomerID | CustomerName | ContactName | Address | City | PostalCode |
| 1 | Tamba Bay Buccaneers | Tom Brady | 1 Buccaneer Place | Tampa, FL | 33607 |
| 2 | New England Patriots | NULL | 1 Patriots Place | Foxborough, MA | 02035 |
| 3 | Dallas Cowboys | Dak Prescott | 1 Cowboys Way Suite 100 | Frisco, TX | 75034 |
| 4 | Kansas City Chiefs | Patrick Mahomes | 1 Arrowhead Drive | Kansas City, MO | 64129 |
| 5 | New Orleans Saints | NULL | 5800 Airline Drive | Metairie, LA | 70003 |
Using IS NULL operator will test for empty values. The following query lists all customers with a NULL value in “ContactName”:
IS NULL operator example
SELECT CustomerName, ContactName, City FROM Customers WHERE ContactName IS NULL;
4. The IS NOT NULL Operator
The IS NOT NULL operator will test for non-empty values. Still using the table above, let’s query to list all customers with a NOT NULL value in the field “ContactName”.
IS NOT NULL operator example
SELECT CustomerName, ContactName, City FROM Customers WHERE ContactName IS NOT NULL;
5. Summary
In summary, we saw what’s is a NULL value in a SQL table. We noticed that null values are different from zeros and blank spaces that can be inserted into the table’s field.
Also, we could see the operators IS NULL and IS NOT NULL to test null values in a table example.
6. Download the source code
You can download the full source code of this example here: SQL is null – SQL is not null
Last updated on Jan. 4th, 2021
