sql

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.

sql not null

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.

CustomerIDCustomerNameContactNameAddressCityPostalCode
1Tamba Bay BuccaneersTom Brady1 Buccaneer PlaceTampa, FL33607
2New England PatriotsNULL1 Patriots PlaceFoxborough, MA02035
3Dallas CowboysDak Prescott1 Cowboys Way Suite 100Frisco, TX75034
4Kansas City ChiefsPatrick Mahomes1 Arrowhead DriveKansas City, MO64129
5New Orleans SaintsNULL5800 Airline DriveMetairie, LA70003
Customer table example

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;
IS NULL query result

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;
IS NOT NULL query result

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

Download
You can download the full source code of this example here: SQL is null – SQL is not null

Last updated on Jan. 4th, 2021

Sergio Lauriano Junior

Sergio is graduated in Software Development in the University City of São Paulo (UNICID). During his career, he get involved in a large number of projects such as telecommunications, billing, data processing, health and financial services. Currently, he works in financial area using mainly Java and IBM technologies.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button