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
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
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;
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