Comprehensive Guide to Handling NULL values in SQL

4 most pressing questions about NULL values in SQL answered

6 min read – Mar 30, 2023

Comprehensive Guide to Handling NULL values in SQL — Image by Author

Handling NULL values in SQL is an important aspect of database design and management. In SQL, NULL values are used to represent missing or unknown data. Almost all research experiences the issue of missing data, even in cases where the study is well-designed and well-controlled. They are problematic and managing NULL values in relational database management systems (RDBMSs) is a challenge that remains unresolved, but handling NULL values in SQL is crucial because:

1. They can lead to incorrect or unexpected results during data manipulation and retrieval.

2. They can cause errors or application crashes if not handled properly.

3. Proper handling ensures data integrity and consistency across the database.

The following are some of the most pressing questions users have regarding NULL values in SQL.

1. How to replace NULL values with a default value in SQL?

2. How to filter out rows with NULL values in a specific column?

3. How do NULL values affect aggregate functions, and how to handle them?

4. How do NULL values affect JOIN operations, and how to handle them?

Here are some provisional solutions to the aforementioned questions:

1. How to replace NULL values with a default value in SQL?

Many users ask how to replace NULL values with a default value or an alternative value. This can be achieved using NULL handling functions such as ISNULL() in SQL Server, NVL() in Oracle, or IFNULL() in MySQL.

SELECT CustomerID, 
       CompanyName, 
       ISNULL(Region, 'N/A') AS Region
FROM Customers;

This SQL query retrieves the CustomerID, CompanyName, and Region columns from the Customers table. The ISNULL() function is used to replace any NULL values in the Region column with the string ‘N/A’. The resulting column is aliased as Region. The query returns a result set with all rows from the Customers table, displaying ‘N/A’ instead of NULL values in the Region column.

You can also use the CASE statement to handle NULL values and replace them with default values.

The CASE statement allows you to perform conditional logic in SQL queries, which can be useful for handling NULL values in more complex situations.

SELECT CustomerID, 
       CompanyName,
       CASE
           WHEN Region IS NULL THEN 'N/A'
           ELSE Region
       END AS Region
FROM Customers;

Both SQL queries return the same result set. The ISNULL() function is easier to read.

2. How to filter out rows with NULL values in a specific column?

Another common question is how to filter out or select rows with NULL values in a specific column.

Filtering out rows with NULL values in a specific column can be accomplished using the IS NOT NULL operator in the WHERE clause of your SQL query. This operator allows you to only include rows where the value in the specified column is not NULL. Here’s a step-by-step guide:

Identify the table and the specific column where you want to filter out NULL values.
Use the SELECT statement to retrieve the desired columns from the table.
Add a WHERE clause to the query, specifying the column to be checked and the IS NOT NULL operator.

Here’s an example using the Customers table and filtering out rows with NULL values in the Region column:

SELECT CustomerID, 
       CompanyName, 
       Region
FROM Customers
  WHERE Region IS NOT NULL;

In this example, the query returns a result set containing the CustomerID, CompanyName, and Region columns for all rows in the Customers table where the Region column does not have a NULL value. Rows with NULL values in the Region column are excluded from the result set.

3. How do NULL values affect aggregate functions, and how to handle them?

To handle NULL values in aggregate functions, you can use NULL handling functions like ISNULL(), NVL(), or IFNULL() to replace NULL values with a default or specific value before performing the aggregation. Additionally, you can filter out rows with NULL values using the WHERE clause or include NULL values as a separate category in a GROUP BY clause.

As an example, the SUM() function calculates the total sum of non-NULL values in a given column. When using the SUM() function, NULL values are ignored, and they do not contribute to the total sum. However, there may be situations where you want to treat NULL values as zeros in your calculations. In such cases, you can use NULL handling functions to replace NULL values with zeros before performing the SUM() operation.

Here’s an example using the OrderDetailstable, which has a ProductID, Quantity, and Price column. Suppose we want to calculate the total revenue and consider NULL values in the Price column as zeros:

SELECT 
  SUM(ISNULL(UnitPrice, 0) * Quantity) as TotalRevenue
FROM OrderDetails;

In this query, we use the NULL handling functions (ISNULL() for SQL Server, NVL() for Oracle, and IFNULL() for MySQL) to replace NULL values in the Price column with zeros. The SUM() function then calculates the total revenue based on the non-NULL Price values and the substituted zeros for NULL values.

4. How do NULL values affect JOIN operations, and how to handle them?

Another pressing question is how NULL values influence JOIN operations, especially when using INNER JOIN and OUTER JOIN. It is important to know how to include or exclude rows with NULL values when joining tables.

When joining tables on columns that contain NULL values, use OUTER JOIN (either LEFT OUTER JOIN, RIGHT OUTER JOIN, or FULL OUTER JOIN) to include rows with NULL values in the result set.

Example:

-- You can write LEFT OUTER JOIN or LEFT JOIN. We generally write LEFT JOIN

SELECT C.CustomerID, C.CompanyName, O.OrderID
FROM Customers C
LEFT OUTER JOIN Orders O ON C.CustomerID = O.CustomerID;

An INNER JOIN returns only those rows where there is a match in both tables. If a NULL value is present in the joining column(s), the row will not be included in the result set, as NULL values are considered unknown and not equal to any value, including other NULLs. To handle this, you can either use OUTER JOINs or filter out NULL values in the joining columns using the WHERE clause.

Conclusion

In conclusion, understanding and handling NULL values in SQL is a critical aspect of database management, as they can affect data integrity, query performance, and result accuracy. Throughout this discussion, I explored some of the solutions you can use to to intereact with NULL values in various SQL operations, such as NULL replacement and filtering, aggregate functions, and JOIN operations.

By learning the best practices, tips, tricks, and solutions to handle NULL values effectively, database professionals can better manage their databases, ensure data consistency, and avoid errors or unexpected results in their database operations. Proper handling of NULL values in SQL is crucial for maintaining a reliable and efficient database system.

References:

Toussaint, E., Guagliardo, P., Libkin, L., & Sequeda, J. (2017). Troubles with Nulls, Views from the Users. Proceedings of the 36th ACM SIGMOD-SIGACT-SIGAI Symposium on Principles of Database Systems, 51–62. doi: 10.1145/3035918.3035931 https://www.vldb.org/pvldb/vol15/p2613-guagliardo.pdf
Kang H. The prevention and handling of the missing data. Korean J Anesthesiol. 2013 May;64(5):402–6. doi: 10.4097/kjae.2013.64.5.402. Epub 2013 May 24. PMID: 23741561; PMCID: PMC3668100. https://www.ncbi.nlm.nih.gov/pmc/articles/PMC3668100/

Handling NULL Values in Different database