SQL - COALESCE and NULL Handling Techniques in SQL

Introduction

In SQL, NULL represents a missing, unknown, or unavailable value. It is important to understand that NULL is not the same as zero, an empty string, or a blank space. For example, if a customer has not provided a phone number, the PhoneNumber column may contain NULL.

When working with databases, NULL values can cause unexpected results in calculations, comparisons, sorting, and reporting. SQL provides several techniques for handling NULL values, and one of the most useful is the COALESCE() function.

Understanding NULL in SQL

Consider a table named Employees:

CREATE TABLE Employees (
    EmployeeID INT,
    EmployeeName VARCHAR(100),
    PhoneNumber VARCHAR(20),
    Salary DECIMAL(10,2)
);

Suppose the table contains:

EmployeeID | EmployeeName | PhoneNumber | Salary
------------------------------------------------
1          | Rahul        | 9876543210  | 40000
2          | Priya        | NULL        | 45000
3          | Arun         | 9123456789  | NULL

Here, Priya's phone number is NULL because it is unavailable, while Arun's salary is NULL because the value has not been entered.

A common mistake is to compare NULL using the equality operator:

SELECT *
FROM Employees
WHERE PhoneNumber = NULL;

This does not correctly identify NULL values. SQL uses special operators for checking NULL.

To find NULL values, use:

SELECT *
FROM Employees
WHERE PhoneNumber IS NULL;

To find values that are not NULL:

SELECT *
FROM Employees
WHERE PhoneNumber IS NOT NULL;

What Is COALESCE()?

COALESCE() is an SQL function that returns the first non-NULL value from a list of expressions.

The general syntax is:

COALESCE(value1, value2, value3, ...);

SQL checks the values from left to right. As soon as it finds a value that is not NULL, it returns that value.

For example:

SELECT COALESCE(NULL, NULL, 'Hello', 'World');

The result is:

Hello

The first two values are NULL, so SQL continues checking until it reaches Hello.

Basic Example of COALESCE()

Suppose the Employees table contains:

EmployeeName | PhoneNumber
--------------------------
Rahul        | 9876543210
Priya        | NULL
Arun         | 9123456789

You can replace the NULL phone number with a meaningful message:

SELECT
    EmployeeName,
    COALESCE(PhoneNumber, 'Phone number not available') AS Phone
FROM Employees;

The result will be approximately:

EmployeeName | Phone
------------------------------
Rahul        | 9876543210
Priya        | Phone number not available
Arun         | 9123456789

The original NULL value in the database is not changed. COALESCE only changes how the value is displayed in the query result.

COALESCE() With Multiple Columns

One of the most useful applications of COALESCE is selecting the first available value from multiple columns.

Consider a Customers table:

CustomerName | MobileNumber | HomeNumber | OfficeNumber
---------------------------------------------------------
Ravi         | NULL         | 0801234567 | 0807654321
Anita        | 9876543210   | NULL       | 0802222222
Kiran        | NULL         | NULL       | 0803333333

If you want to display whichever phone number is available, you can write:

SELECT
    CustomerName,
    COALESCE(MobileNumber, HomeNumber, OfficeNumber) AS ContactNumber
FROM Customers;

SQL checks the columns in this order:

1. MobileNumber

2. HomeNumber

3. OfficeNumber

For Ravi, MobileNumber is NULL, so SQL uses HomeNumber.

For Anita, MobileNumber is available, so SQL uses it immediately.

For Kiran, both MobileNumber and HomeNumber are NULL, so SQL uses OfficeNumber.

COALESCE() in Calculations

NULL values can affect arithmetic operations.

Suppose an employee table contains:

EmployeeName | Salary | Bonus
------------------------------
Rahul        | 40000  | 5000
Priya        | 45000  | NULL

If you calculate total income as:

SELECT
    EmployeeName,
    Salary + Bonus AS TotalIncome
FROM Employees;

For Priya, the result may be NULL because adding a number to NULL produces NULL.

You can handle this using COALESCE:

SELECT
    EmployeeName,
    Salary + COALESCE(Bonus, 0) AS TotalIncome
FROM Employees;

Now, if the bonus is NULL, SQL treats it as zero for this calculation.

The result becomes:

EmployeeName | TotalIncome
--------------------------
Rahul        | 45000
Priya        | 45000

This technique is particularly useful when calculating totals, discounts, commissions, taxes, expenses, and other numerical values.

COALESCE() With Aggregation

Aggregate functions such as SUM() can also produce NULL in certain situations, particularly when there are no non-NULL values to aggregate.

For example:

SELECT COALESCE(SUM(Bonus), 0) AS TotalBonus
FROM Employees;

If there are no available bonus values, the query returns 0 instead of NULL.

This is useful when generating reports where displaying zero is more meaningful than displaying NULL.

Difference Between NULL and Zero

NULL and zero have completely different meanings.

For example:

Salary = 0

means the salary value is explicitly zero.

Whereas:

Salary = NULL

means the salary value is unknown, missing, or unavailable.

Therefore, replacing every NULL with zero should only be done when zero is logically appropriate.

For example:

SELECT COALESCE(Salary, 0)
FROM Employees;

may be appropriate for a report where missing salary values should be represented as zero, but it may be misleading if NULL actually means "salary information not provided."

Other NULL Handling Techniques

COALESCE is not the only way to work with NULL values.

The IS NULL operator is used to find NULL values:

SELECT *
FROM Employees
WHERE Salary IS NULL;

The IS NOT NULL operator finds values that are present:

SELECT *
FROM Employees
WHERE Salary IS NOT NULL;

SQL also provides NULLIF(), which can convert a specified value into NULL.

For example:

SELECT NULLIF(10, 10);

The result is:

NULL

But:

SELECT NULLIF(10, 5);

returns:

10

This can be useful when dealing with special values that should be treated as missing.

COALESCE() and NULLIF() Together

These functions can sometimes be combined.

Suppose an application stores an empty string when a phone number is unavailable. You can convert the empty string to NULL using NULLIF() and then provide a replacement using COALESCE():

SELECT
    COALESCE(NULLIF(PhoneNumber, ''), 'Not Available') AS Phone
FROM Employees;

Here, NULLIF() checks whether the phone number is an empty string. If it is, it converts it to NULL. COALESCE() then replaces that NULL with Not Available.

Important Points to Remember

COALESCE returns the first non-NULL expression from left to right. It does not permanently modify the data stored in the database. It is commonly used for displaying default values, selecting the first available value from several columns, preventing NULL from affecting calculations, and creating cleaner reports.

NULL should not be confused with zero or an empty string. Use IS NULL and IS NOT NULL when checking for NULL values. When replacing NULL values with another value, always consider what the NULL actually represents before choosing a replacement.

Conclusion

COALESCE() and NULL handling techniques are essential for writing reliable SQL queries. Real-world databases frequently contain missing or unavailable information, and ignoring NULL values can produce incorrect calculations or incomplete reports. COALESCE provides a simple way to select the first available value, supply meaningful defaults, and safely handle NULL values in calculations and query results. Understanding COALESCE together with IS NULL, IS NOT NULL, and NULLIF() helps developers write more robust and practical SQL queries.