Find Null or Empty Values in SQL

1. Introduction

Managing null or empty values in SQL is a frequent task for database developers and administrators, as these values represent missing or undefined data. It’s essential to identify them to maintain data integrity and ensure accurate query results.

In this tutorial, we’ll discuss different ways to retrieve rows containing such values. We tested our examples on MS SQL Server 2022, PostgreSQL 14, and MySQL 8 databases. However, most of the methods we discuss should be available in other versions of these SQL implementations and other SQL dialects.

2. NULL and Empty Values

Before starting to write the SQL query, it’s essential to understand the distinctions between NULL and empty values (strings).

NULL values in SQL represent missing or unknown data in a database column. This can occur for two reasons: the data does not exist, or it exists but is currently unknown. In contrast, an empty value, such as an empty string, is a specific value for the corresponding column type to which we assign a special meaning similar to NULL. Further, NULL can be assigned to a column of any data type, whereas empty strings only apply to string columns and cannot be used for other data types, such as numeric.

We often refer to NULLs as representing missing values. However, an empty string can also indicate the absence of data. Usually, we can use them to identify different types of missing data. In some cases, NULL can be used for unknown data, while an empty string can signify nonexistent data. For instance, when storing personal information such as a middle name in a column, a NULL value can indicate that the middle name wasn’t provided, whereas an empty value means the person doesn’t have a middle name. In queries, we can search for rows containing NULL values, empty strings, or both.

In this tutorial, we’ll use the Department table defined in this schema. Let’s insert some data with empty strings and NULLs:

INSERT INTO Department (id, name, code) VALUES
  (1001, 'Psychology', ''),
  (1002, 'Statistics', NULL); 

After the insertion, the Department table now includes the following rows:

Our queries should retrieve the entries for Psychology and Statistics from the table, as they contain either NULL or empty values for the column code. 

3. Standard Approaches

In this section, we’ll examine approaches that adhere to ANSI SQL standards, ensuring compatibility across major relational databases.

3.1. Using IS NULL Operator

Let’s write the SQL query to filter the rows with empty strings or null values in the code column:

SELECT id, name FROM Department 
WHERE code IS NULL 
OR code = '';

Here, we used the OR condition within the WHERE clause to combine two conditions. The IS NULL operator checks whether the column contains null values. To detect empty values within the column, we compare it against the empty string (”).

Sometimes, a column might contain values that consist only of spaces, especially when data is imported from various sources. Typically, these space-only values are treated as empty since they don’t contain any meaningful characters. To catch such rows, we can trim the spaces before checking for empty values:

SELECT id, name FROM Department 
WHERE code IS NULL 
OR TRIM(code) = '';

The TRIM function removes whitespaces from a string’s beginning and end, allowing us to compare it against an empty character.

3.2. Using COALESCE

Another approach is using the COALESCE function:

SELECT id, name FROM Department 
WHERE TRIM(COALESCE(code, '')) = '';

The COALESCE function in SQL returns the first non-NULL value from a list of arguments.

COALESCE(code, ”) returns the value of the column code if it is not NULL. Otherwise, it returns an empty string. The TRIM function removes any leading or trailing spaces in the column value so that a string containing only blank spaces is treated as empty. Finally, the query filters rows where the trimmed code value is an empty string. Combining the COALESCE function with TRIM allows us to retrieve rows containing empty or NULL values effectively.

4. Database Specific Approaches

Apart from the ANSI SQL standards, some database management systems and dialects of SQL provide database-specific functions and operators to handle NULL values. In this section, we’ll show the methods specific to PostgreSQL, MySQL, and MS SQL.

4.1. Using NULLIF

We can use the function NULLIF to compare and check if the column contains null or empty values:

SELECT id, name FROM Department 
WHERE NULLIF(TRIM(code), '') IS NULL;

The NULLIF function evaluates its two arguments, returning NULL if they are equal. When applied to a column containing an empty value, it returns NULL, allowing us to check for NULL using the IS NULL operator.

While not part of the ANSI SQL standard, NULLIF is a common function available in popular database systems like PostgreSQL, MySQL, and MS SQL.

4.2. Using ISNULL

MS SQL provides a function ISNULL to check for null values:

SELECT id, name FROM Department 
WHERE ISNULL(code, '') = '' 
OR TRIM(code) = '';

The ISNULL function in MS SQL takes two arguments: the first is a value or column name, and the second is the default value if the first argument is null. This query retrieves rows where the code column is either null, an empty string, or contains only spaces.

In MySQL, ISNULL(code) returns 1 if the column code is NULL. Otherwise, it’s 0:

SELECT id, name FROM Department 
WHERE ISNULL(code) 
OR TRIM(code) = '';

 This query retrieves rows where the code column is null or contains only spaces.

5. Summary

Here’s a concise overview of the functions and operators we covered in this tutorial.

The operators IS NULL, COALESCE, TRIM, and NULLIF are available in all three databases. However, the ISNULL function is available only in MySQL and MS SQL and has a different syntax in both.

6. Conclusion

In this article, we explored several techniques for identifying NULLs and empty values within a column. In SQL, NULL represents missing or unknown data, covering cases where data is absent or uncertain. We can use empty strings for similar purposes, allowing us to differentiate between the absence and nonexistence of data. 

As always, the queries used in this article are available over on GitHub.