SQL is null – SQL is not null

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.

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

Last updated on Jan. 4th, 2021

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.