Handling NULLs in Calculations with COALESCE()

Today we're diving deeper into handling NULL and getting to know a super useful function — COALESCE(). This function lets you deal with NULL values in your data in a really smooth way.

Check out this example: you have a table with employee data, but some employees are missing salary info. What happens if you try to increase all the salaries? Nothing good. You can't do math with NULL. But what if you want to swap out those NULL salaries for, say, 0? That's where COALESCE() comes in.

COALESCE() is a function that returns the first non-NULL value from the list of arguments you give it. If everything in the list is NULL, it just returns NULL. In plain English, it's like saying: "Give me the first legit value you can find, please!"

Syntax

COALESCE(value1, value2, ..., value_n)

value1, value2, ..., value_n are the arguments you pass to the function. It'll return the first one that's not NULL.

COALESCE() in Action: Examples

Let's look at a couple of examples.

Example 1: Replacing NULL with 0

Let's say we have a salaries table:

We want to calculate the total salary — that's easy:

SELECT SUM(salary) AS total_salary
FROM salaries;

The SUM() function ignores NULLs, so no problem here.

But then we want to calculate the total salary if we give everyone a $1000 bonus.

SELECT SUM(salary+1000) AS total_salary
FROM salaries;

And now the result gets weird. It's way better to get rid of those NULL values right away using COALESCE and swap them for 0. Here's how you do it:

SELECT SUM(COALESCE(salary, 0)) AS total_salary
FROM salaries;

Result:

Way better and more reliable.

Example 2: Replacing NULL with a Default Value

Let's say we have a students table with names and addresses:

We want to replace NULL in addresses with "Not specified":

SELECT name, COALESCE(address, 'Not specified') AS resolved_address
FROM students;

Query result:

Example 3: Using Multiple Values

Sometimes you need to replace NULL not with just one value, but with a whole chain of possible values. For example, you want to pick a name, a nickname, or use "No name" if neither is set. Here's a users table:

Query:

SELECT user_id,
       COALESCE(first_name, short_name, 'No name') AS display_name
FROM users;

Result:

Practical Uses for COALESCE()

In real life, COALESCE() is a lifesaver for dealing with messy data.

Let's see how it helps in different situations.

Example 1: Replacing Values in Text Fields

Original customers table:

Query:

SELECT name, COALESCE(address, 'Not specified') AS address
FROM customers;

Result:

Example 2: Prepping Data for Reports

Original sales table:

Query:

SELECT SUM(COALESCE(price, 0)) AS total_sales
FROM sales;

Result:

Common Mistakes When Using COALESCE()

Even though COALESCE() seems simple and universal, there are a few gotchas to watch out for.

Data type mismatch. All arguments you pass to COALESCE() need to be compatible in terms of data type. For example, you can't mix string values and numbers.

-- Error
SELECT COALESCE(salary, 'Not specified') FROM employees; 
-- salary is a number field, but 'Not specified' is text.

Ignoring argument order. COALESCE() returns the first non-NULL value, so the order of arguments really matters.