Using COALESCE in SQL: A Beginner’s Guide

What is the COALESCE Function in SQL?

Coalesce is essentially a NULL killer. If you have a dataset in SQL that has a lot of NULL values, we can use COALESCE function to replace the NULL values. However, COALESCE is a versatile SQL function that can be used in a wide range of scenarios where NULL values may be present. By allowing you to define default values and handle NULL values more effectively, COALESCE can help you create more robust, error-resistant SQL code.

Pros and Cons of COALESCE

Basic Functionality and Syntax of COALESCE

It returns the first non-NULL value from its arguments. If all arguments are NULL, it returns the default value.

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

We combine this function with a SELECT query in the following manner to retrieve the first non-null value from the column and provide an alternative if conditions are NULL

SELECT COALESCE(column1,'No value found') AS value FROM my_table;

You are not limited to a single column within the function multiple columns can be selected. n this query, column1, column2, and column3 are evaluated by the COALESCE function, and the first non-NULL value is returned. If all three columns are NULL, the function returns the string ‘No value found’.

Use-Cases for COALESCE in SQL

Replacing NULL values with a default value:

SELECT name, COALESCE(phone_number, 'N/A') AS phone_number
FROM customers;

This query will return an alternative provided by the query. In this particular example, a list of customer names and phone numbers. If a customer does not have a phone number in the database, the COALESCE function will replace the NULL value with the string ‘N/A’.

Combining data from multiple columns or tables

SELECT COALESCE(salary1, salary2, salary3) AS salary
FROM employee_salaries;

In this particular query will return a list of employee salaries, selecting the first non-NULL salary value encountered from three different salary columns in the employee_salaries table.

Handling data in conditional expressions with NULLs

SELECT id, name, CASE WHEN COALESCE(salary, 0) > 50000 THEN 'High Salary' ELSE 'Low Salary' END AS salary_type
FROM employees;

If the salary is NULL, the COALESCE function will replace it with a default value of 0.

Reducing the number of NULL in SQL Calculations

SELECT COALESCE(price, 0) * COALESCE(quantity, 1) AS total_cost
FROM orders

The COALESCE function is used to replace NULL values with default values of 0 for the price column and 1 for the quantity column. Alternative to calculation when adding can be seen below

SELECT COALESCE(score1, 0) + COALESCE(score2, 0) + COALESCE(score3, 0) AS total_score FROM exam_scores;

You can see that in both cases, the function is used to replace any NULL values with a default value of 0 to ensure the calculation is accurate.

Dating Formatting when dealing with TEXT.

SELECT CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, '')) AS full_name
FROM customers;

This query will return a list of customer full names, concatenating the first and last name columns. The COALESCE function is used to replace NULL values with empty strings to ensure proper string concatenation.