SQL IS NULL Operator in WHERE Clause
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
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|
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.
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:
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
TRUE? The answer is neither. With the
NULL value in
SQL, there are actually three values that can be returned from a logical expression:
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 (
UNKNOWN are discarded). The result of the query does not include the records with NULL value in price_per_ton:
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:
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.
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
SELECT year, apple_variety, price_per_ton, COALESCE(price_per_ton,0) AS expression FROM apples
Here are the results:
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