SQL IS NULL Operator in WHERE Clause

SQL Tutorial Home

In this lesson we cover the NULL value concept and the SQL IS NULL operator. Both are simple but powerful concepts to add into your set of SQL tools. Continue learning new SQL concepts to create more complex WHERE conditions.

In these example queries, we continue using the apple table used in the previous lesson. As a reminder, apple is a single table database with all the production history for an apple farm. Here is the schema:

Year Apple Variety Number of Trees in Production Tons Produced Harvest day Price per Ton First summer storm
2020 Red Delicious 2000 102 06/23/2020 54.50 07/03/2020
2020 Magic Green 700 33 06/12/2020 62.60 07/03/2020
2020 Red Globus 500 26 05/30/2020 71.50 07/03/2020
2019 Red Delicious 1800 87 07/15/2019 52.25 07/12/2019
2019 Magic Green 500 26 06/28/2019 59.40 07/12/2019
2019 Red Globus 500 27 05/28/2019 68.00 07/12/2019
2018 Red Delicious 1800 92 07/02/2018 56.75 06/03/2018
2018 Red Globus 500 24 05/30/2018 66.00 06/03/2018
2017 Red Delicious 1500 76.5 07/18/2017 51.45 07/30/2017
2016 Red Delicious 1500 72 06/26/2016 47.60 06/23/2016
2015 Red Delicious 1500 68 07/02/2015 NULL NULL
2014 Red Delicious 1500 71 06/28/2014 NULL NULL

SQL NULL Value: What is it?

Suppose you started to register the date of first_summer_storm in 2016, after reading about apple production losses caused by summer storms. The question is, if you don’t know the first_summer_storm date for years prior to 2016 what value should you put into the column first_summer_storm? The reply is simple: a NULL value, meaning you do not have a value for the column.

A SQL NULL value in any column of a record indicates the value in unknown.

The same occurs with the column price_per_ton for years 2014 and 2015. Assign NULL values to the column price_per_ton for the records of years 2014 and 2015 as you can see in the apple table.

SQL IS NULL Operator

You need a way to identify records with the NULL value: the solution is to use the SQL IS NULL operator. Here is an example query:


SELECT year, apple_variety, tons_produced
FROM   apples
WHERE  first_summer_storm IS NULL

Here are the results:

year apple_variety tons_produced
2015 Red Delicious 68
2014 Red Delicious 71

The opposite operator is IS NOT NULL, that you can use to identify records with NON NULL values in any given column. For example:


SELECT year, apple_variety, tons_produced
FROM   apples
WHERE  first_summer_storm IS NOT NULL

There is an interesting issue due to the NULL value representing an unknown value. Here is a question:

What results do you expect from this query?


SELECT year, apple_variety, price_per_ton
FROM   apples
WHERE  price_per_ton > 60

This question can be rephrased as: Is price_per_ton > NULL FALSE or TRUE? The answer is neither. With the NULL value in SQL, there are actually three values that can be returned from a logical expression:

  • TRUE
  • FALSE
  • UNKNOWN

In this case, the result of price_per_ton > NULL is UNKNOWN. The WHERE clause filters to the result all the records having a TRUE value for the WHERE condition (FALSE and UNKNOWN are discarded). The result of the query does not include the records with NULL value in price_per_ton:

year apple_variety tons_produced
2020 Magic Green 62.60
2020 Red Globus 71.50
2019 Red Globus 68.00
2018 Red Globus 66

For example:


SELECT year, apple_variety, price_per_ton
FROM   apples
WHERE  price_per_ton <= 60

The results do not include records with NULL in price_per_ton:

year apple_variety price_per_ton
2020 Red Delicious 54.50
2019 Red Delicious 52.25
2019 Magic Green 59.40
2018 Red Delicious 56.75
2017 Red Delicious 51.45
2016 Red Delicious 47.60

Looking at both queries, it appears that data is missing, as there are less rows returned than the total number of rows in the table. This is because the NULL values are not included in any of the results.

PostgreSQL COALESCE Function

A method to replace SQL NULL values with a non-null value is available in some SQL dialects. For example, PostgreSQL offers the COALESCE function. Other dialects may have this option as ISNULL or similar. This function receives two parameters, and returns the first non-null parameter value. Here is a simple query to show the original value of the column price_per_ton and the value returned by the PostgreSQL function COALESCE:


SELECT year, apple_variety,
       price_per_ton,     
       COALESCE(price_per_ton,0) AS expression
FROM   apples

Here are the results:

year apple_variety price_per_ton expression
2020 Red Delicious 54.50 54.50
2020 Magic Green 62.60 62.60
2020 Red Globus 71.50 71.50
2019 Red Delicious 52.25 52.25
2019 Magic Green 59.40 59.40
2019 Red Globus 68.00 68.00
2018 Red Delicious 56.75 56.75
2018 Red Globus 66. 66.
2017 Red Delicious 51.45 51.45
2016 Red Delicious 47.60 47.60
2015 Red Delicious NULL 0
2014 Red Delicious NULL 0

Closing Words

The SQL NULL value is a powerful concept in SQL enabling you to represent unknown values in database columns. This lesson covered concepts related to NULL, including SQL IS NULL and SQL IS NOT NULL operators and the PostgreSQL COALESCE() feature.There are many more new concepts to learn about WHERE clauses. Keep going, learn SQL and increase your skills!

IN THIS PAGE