How to use NULL or empty string in SQL

sql

best-practices

data-types

performance

byAnton Shumikhin·Jan 3, 2025

The key difference is that NULL and '' (empty string) are not the same in SQL. To find NULL values, use IS NULL, and to spot empty strings, compare with ''. Below is an example to select rows fulfilling either condition:

SELECT * FROM your_table WHERE your_column IS NULL OR your_column = '';

Remember that = can't match NULL values, which is why using IS NULL is a must. This query will fetch all rows with NULL or empty values in your_column.

Deep Dive: Handling NULL and Empty Strings

1. Embracing COALESCE and IFNULL

While crafting SQL queries, the COALESCE and IFNULL functions come in handy. They allow treating NULL as an empty string or replacing it with another value. For example, in replacing NULLs:

-- "Replacement" is replacing the NULL value.
SELECT COALESCE(your_column, 'replacement') FROM your_table;

You can also use IFNULL similarly in MySQL:

-- "Goodbye NULL, hello replacement".
SELECT IFNULL(your_column, 'replacement') FROM your_table;

These functions are beneficial when you want to replace NULL values in your result set with a default value.

2. The Power of NULLIF and LTRIM/RTRIM

With NULLIF, you can make your column return NULL when it matches certain condition. If we team this up with LTRIM and RTRIM, we can combat pesky whitespace bursting strings effectively:

-- The SQL equivalent of looking for a needle in a haystack.
SELECT ISNULL(NULLIF(LTRIM(RTRIM(your_column)), ''), 'replacement') FROM your_table;

This query nicely replaces only genuinely empty fields (whether filled with spaces or standing empty) with a 'replacement' value.

3. Exclusion Strategy: NULL, Empty Strings, and White Spaces

There might be situations where you want to exclude invalid data, which includes both NULLs and whitespace. This condition ensures your data is neither NULL, an empty string, nor just spaces:

-- We don't like NULLS, empty strings, or spaces here.
SELECT * FROM your_table WHERE ISNULL(LTRIM(RTRIM(your_column)), '') <> '';

Making Most of NULL and Empty Strings: Best Practices

1. Be mindful of Data Types

While dealing with NULL and empty strings, remember data types. In some databases, like Oracle, an empty string is considered NULL. Therefore, it's best to validate your findings with your DBMS to ensure correct behavior.

2. Keep Performance in Check

The way you index columns with NULL and empty strings can greatly influence performance. Proper indexing can turbocharge query speed, especially when dealing with larger datasets.

3. Troubleshooting Tips

When your queries behave like stubborn mules, remember the following:

Spot whitespace using functions like LEN() or DATALENGTH().
Pattern matching (LIKE) can help identify if strings are keeping spaces hostage, for instance, your_column LIKE ' %'.
Always confirm data types and collation settings to ward off unexpected outcomes when comparing strings.