How to Write SQL Queries | Understanding NULL Values
So far in this series, we have looked at
- Database fundamentals
- Simple SELECTS
- Column Expressions
- Filter with WHERE
- Using LIKE for Wildcard searches
- Using ORDER BY to sort our results
In this final post on Single Table SELECT statements and before we move on to retrieving data from multiple tables it is important we introduce NULL Values. If you are new to SQL this will be an important consideration when writing your query and you will need to build your understanding of the database you are querying and whether the column in the table your are querying allows null values. A column in a table can either allow nulls or not allow nulls and you will need to write query accordingly. Of “it depends” on the type of query you write
So let’s revisit our syntax of the SELECT statement
The Basics of a SELECT query
So this is the basis syntax we have been discussing so far
SELECT <Column Expression>,...,<Column Expression> | *
FROM <table> [JOIN <table> ON <join condition>]
WHERE <filter condition>
GROUP BY <column>,...,<column>
HAVING <filter condition>
ORDER BY <column>,...,<column>
There are no new explanations in this post of the clauses listed here, we are going to discuss how NULL values can impact your query results
What are NULL Values?
A NULL is an unknown or missing value. The data doesn’t exist in the database. If your column ALLOWS NULL then when you insert data into that table the database engine won’t force you to enter a value for that column. If you don’t specify a value for that column when inserting a row, the value in the column will be marked as a NULL. If the column does not allow NULL you will need to specify a value for that column when performing the insert.
Again we are not going to discuss arguments for and against NULL values here, but as someone new to SQL. understanding if your table columns allow Nulls or not will be important. If you understand them and understand how they behave and how to handle them it will help you avoid unexpected or erroneous results. There are functions in SQL that can be used to help you deal with NULLs
So null is an unknown or missing value, NULL IS NOT:
- The same as zero
- The same as blank
- and it’s important to remember it’s not the string “NULL” either
So with this in mind let’s look at an example
 NULL Example
For our NULL example, we will continue with AdventureWorksLT2019 database that we have been using throughout this series. But we will now write a single-table SELECT to retrieve data from the [SalesLT].[Product] table
SELECT [ProductID]
,[Name]
,[ProductNumber]
,[Color]
,[StandardCost]
,[ListPrice]
,[Size]
,[Weight]
FROM [SalesLT].[Product]
If you execute this query you will see, by coincidence, that the size and weight columns allow nulls.
If your database is SQL Server there are numerous ways to check if a column allows NULLs. We will keep it simple and use the GUI. If I expand the database, and then the table and then the column folder you can see quite quickly what columns allow nulls and which ones don’t.
So what? I bet some of you are asking yourself.
The Problem with NULL
Problem is probably the wrong word but I expect I have got your attention.
To demonstrate NULL behaviour and why you need to be aware of it I am going to look to do a calculation in my SELECT. I will look to multiply Size by Weight. This will demonstrate that a NULL value in an expression will cause the result to be NULL. In the following example, I calculate the cost to weight ratio by dividing Cost by weight
SELECT [ProductID]
,[Name]
,[ProductNumber]
,[Color]
,[StandardCost]
,[ListPrice]
,[Size]
,[Weight]
,StandardCost/ Weight as CostToWeightRatio
FROM [SalesLT].[Product]
A little contrived I know, but imagine that was an employees table and you were adding salary (not null) to commission (null) to calculate someones pay. Then it becomes a problem you will need to handle.
Useful Functions for Working with Nulls
What you do with NULLs is likely to be a business decision. In the commission + Salary scenario, you might simply make commission zero and move. In the example of CostToWeightRate dividing by zero is not going to so we might decide to give an arbitrary value of 1 or we might decide to handle this in some other way.
How do you handle NULLs?
Good question, you might want to make those unknown or missing values into something this known. You might decide to make your NUlls say zero if it makes business sense to do so. In which case,  there is a function, well technically functions to help us that.
We will look at COALESCEÂ in this example which is part of the ISO/ANSI standard. There are product specific functions that work in a similar way. ISNULL in TSQL is an example of a product-specific function
You can use this to take the NULL value and turn it into something more useful. COALESCE takes a number of parameters and returns the first non-null (or known value) that it finds. You can think of it as a series of IF THEN ELSE statements,t this my attempt at pseudocode for i
IF P1 IS NOT NULL THEN RETURN P1
ELSE IF P2 IS NOT NULL THEN RETURN p2
ELSE IF ...
ELSE RETURN NULL
Let’s tweak our code from earlier to use the COALESCE function to turn our null values in the weight column and make them the value 1
SELECT [ProductID]
,[Name]
,[ProductNumber]
,[Color]
,[StandardCost]
,[ListPrice]
,[Size]
,[Weight]
,COALESCE (Weight,1) as COALEASCEWeight
,StandardCost/ COALESCE(Weight,1) as CostToWeightRatio
FROM [SalesLT].[Product]
You can see we have handled the NULLs and we get a known CostToWeight value  returned for each row
For ISNULL function in SQL Server check out this Microsoft Document
NULLS and the WHERE Clause
If you have NULLs in your data then finding and filtering with a WHERE clause needs special attention. When an unknown is tested in a condition, the result will be unknown. The WHERE clause decides whether or not to include the row. If it is not certain the condition is true it will assume it to be false, therefore NULLs will be evaluated to be false and not included in the results
What if you want to filter on NULLs, there is a test operator called IS NULL and IS NOT NULL that can be used to test for NULLs
IS NULL
The test operator IS NULL returns true when the operand is NULL.
IS NOT NULL
The test operator IS NOT NULL returns true when the operand is not NULL.
Rules around NULLs in a condition
You need to consider the following when working with NULLs in a condition
- NULL is not equal to any value
- NULL is not different from any value
- NULL is not greater or less than any value
- NULL is not equal to NULL
- NULL is not different from NULL
- NULL is not greater or less than NULL
- You need to use IS NULL and IS NOT NULL test operators when working with NULLs in a condition
NULLS in a Condition Examples
Again, I think NULLs are best described and understood with an example. We will continue with the example above.
SELECT [ProductID]
,[Name]
,[ProductNumber]
,[Color]
,[StandardCost]
,[ListPrice]
,[Size]
,[Weight]
,COALESCE (Weight,1) as COALEASCEWeight
,StandardCost/ COALESCE(Weight,1) as CostToWeightRatio
FROM [SalesLT].[Product]
WHERE Weight = NULL
You can see here in the WHERE clause we are using the equals (=) operator. This query will return no rows. Try it for yourself. Weight can never equal NULL because we don’t know what NULL is, it’s missing. So we would need to find the NULLs using IS NULL
SELECT [ProductID]
,[Name]
,[ProductNumber]
,[Color]
,[StandardCost]
,[ListPrice]
,[Size]
,[Weight]
,COALESCE (Weight,1) as COALEASCEWeight
,StandardCost/ COALESCE(Weight,1) as CostToWeightRatio
FROM [SalesLT].[Product]
WHERE Weight IS NULL
This will return all the rows that have an unknown/missing value in their weight column. 97 in our case.
We change the above query to us IS NOT NULL and it would return all the rows with a known value for the weight column
Nulls and Aggregate Function
We haven’t spoken about aggregate functions yet and we will revisit this topic when discussing aggregate functions in more detail. If you are completely new to SQL when I talk about aggregate functions I’m talking about functions, such as SUM, COUNT, AVG etc.
For now, just remember that aggregate functions generally ignore NULL values. This is especially important for the AVG function. If we calculated the mean of the weight columns in our database, the AVG function would ignore the rows with a NULL weight so it would total the known weights and divide by the known instance of weight, discarding the unknowns so it would impact on the result of AVG. This might be the desired outcome, but also it might not be what we want. In which case we would need to handle the NULLs when calculating the mean.
As I said I will revisit this in future posts but it is worth mentioning here
Other Useful Information
If you are interested in attending some formal training the check out our Writing SQL Queries training page. If you would like to speak to us directly please use this contact form below:
Summary
In this post, we have looked at how we can deal with NULLs. A Null is quite difficult to define. However, I think of it as a missing or unknown piece of data. Whether or not a column can contain NULLs will depend on the database design and the properties of the column in the database. If you have NULLs you need to understand how the database engine deals with them. With this understanding, you can write queries that deal with NULL value appropriately and ensure you get the correct results when writing your queries.
Useful Links
Check out our data analysis videos on YouTube
If you want to read our How to Write SQL Queries in order, then the links and the order are provided below
- Database fundamentals – How to Write SQL Queries series.
- Simple SELECTS –Â How to Write SQL Queries series.
- Column Expressions – How to Write SQL Queries series.
- Filter with WHERE – How to Write SQL Queries Series
- Using LIKE for Wildcard searches – How to Write SQL Queries
- Using ORDER BY to sort our results
Four SQL Server problems you might be suffering from – PetchaKutcha Style
0 Comments