Understanding COALESCE () Function in SQL

Since every text string concatenation involving a NULL value returns NULL, the outcome above is NULL. The problem is, however, solved using the coalesce (). Using this function, you return an empty string (or a space) instead of NULL. For example, suppose you are listing car names with their manufacturers; here’s your query.

SELECT 
car || ‘, manufacturer: ‘ || COALESCE(manufacturer, ‘—') AS car_brand
FROM stock

If the manufacturer is NULL, you will have the ‘–‘ instead of NULL. Here are the expected results.

As you can see, the NULL results are eliminated, with the option of inserting your replacement string value.

SQL Coalesce Function and Pivoting

SQL pivoting is a technique used for transforming rows into columns. It allows you to transpose (rotate) data from the “normalized” form (with many rows and fewer columns) to the “denormalized” (fewer rows and more columns). The coalesce function can be used with SQL pivoting to handle null values in pivoted results.

When you PIVOT in SQL, transform rows into columns; resultant columns are aggregate functions of some data. If, in any case, an aggregation results in null for a particular cell, you can use `COALESCE` to replace the null values with a default value or meaningful representation. Below is an example.

Consider a table, sales, with the columns year, quarter, and revenue, and you’d want to pivot data; such that you have years as columns and the sum of revenue for each quarter as the values. But, some quarters have no revenue data, giving null values in the pivoted result. In this case, you can use COALESCE to replace null values in the pivoted result with a zero (0).

SELECT
    year,
    COALESCE(SUM(CASE WHEN quarter = 'Q1' THEN revenue END), 0) AS Q1_Revenue,
    COALESCE(SUM(CASE WHEN quarter = 'Q2' THEN revenue END), 0) AS Q2_Revenue,
    COALESCE(SUM(CASE WHEN quarter = 'Q3' THEN revenue END), 0) AS Q3_Revenue,
    COALESCE(SUM(CASE WHEN quarter = 'Q4' THEN revenue END), 0) AS Q4_Revenue
FROM sales
GROUP BY year;

Scalar User-defined Function and SQL Coalesce Function

You can use scalar UDFs and coalesce to perform complex logic that handles null values. Combining these features will help you achieve more sophisticated data transformations and calculations in SQL queries. Consider a table, Employees, with this structure.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Salary INT,
    Bonus INT
);

You might want to calculate each employee’s total earnings (salary plus bonus). However, there are some missing values. In this case, your scalar UDF can handle the additions of salary and bonus, while coalesce handles the null values. Here’s the scalar UDF for total earnings.

CREATE FUNCTION dbo.CalculateTotalEarnings (@salary INT, @bonus INT)
RETURNS INT
AS
BEGIN
    DECLARE @totalEarnings INT;
    SET @totalEarnings = @salary + COALESCE(@bonus, 0);
    RETURN @totalEarnings;
END;
You can then use the scalar UDF with coalesce in a query:
SELECT EmployeeID, FirstName, LastName,
       Salary, Bonus, dbo.CalculateTotalEarnings(Salary, Bonus) AS TotalEarnings
FROM Employees;

Data Validation using SQL Coalesce

When working with databases, you may want to validate numeric values. For instance, let’s say you have the columns product_name, price, and discount in a table, products. You want to retrieve each item’s product names, prices, and discounts. But, you’d like to treat all NULL discount values as 0. The coalesce function can be helpful. Here’s how to use it.

SELECT product_name, price, COALESCE(discount, 0) AS discount 
FROM products

SQL Coalesce and Computed Columns

Computed columns are virtual columns calculated based on expressions or other columns within a table. Because computed columns are not physically stored in the database, you can leverage them with the coalesce function when handling complex scenarios and transformations. Here’s a practical use-case example.

Consider a `products` table with the columns `price`, `discount`, and `tax_rate`. In this case, you want to create a computed column, `total_price`, to represent the final product price after applying discount and tax. If either discount or tax is not specified (NULL), you’d want to proceed with your calculations using a zero. Here’s how to leverage coalesce to suit the operation.

CREATE TABLE products(
price DECIMAL(10, 2),
discount DECIMAL(10, 2),
tax_rate DECIMAL(5, 2),
total_price AS (COALESCE(price, 0) – COALESCE(price*discount, 0))* COALESCE(1+tax_rate, 1)
);

In the above code, here’s what happens.

1. The total_price computed column is defined as (COALESCE(price, 0) – COALESCE(price*discount, 0))* COALESCE(1+tax_rate, 1).
2. If the price is NULL, COALESCE(price*discount, 0) ensures that it is treated as 0.
3. If the discount is null, COALESCE(price*discount) ensures it is treated as 0, and the multiplication does not affect the calculation.
4. If the tax_rate is NULL, COALESCE(1 + tax_rate, 1) ensures that it is treated as 0, meaning no tax is applied, and the multiplication does not affect the calculation.

The setup above allows you to generate the total_price, a computed column, with the actual final price, despite missing or having NULL values.

SQL Coalesce and CASE Expression

You can syntactically use coalesce through the CASE expression. Here’s an example:

SELECT
Productname + ‘ ’+ deliverydate productdetails,
dealer,
CASE
WHEN cellphone is NOT NULL Then cellphone
WHEN workphone is NOT NULL Then workphone
ELSE ‘NA’
END
EmergencyContactNumber
FROM
dbo.tb_EmergencyContact

In the above setup, CASE queries like the COALESCE function.

Additionally, using the COALESCE and CASE expressions in the same query is possible. The two techniques can handle NULL values and apply conditional logic simultaneously. Let’s illustrate this with an example.

Consider a case where you have a table, products with the columns product_id, product_name, price, and discount. Some of your products have a specific discount while others do not. If a product has a discount, you want to show the discounted price, otherwise, the regular should be displayed.

SELECT 
    product_id,
    product_name,
    price,
    COALESCE(
        CASE
            WHEN discount > 0 THEN price - (price * discount / 100)
            ELSE NULL
        END,
        price
    ) AS discounted_price
FROM products;

In the above code, the `CASE` checks if the `discount` is greater than zero, and calculates discounted price, else it returns a NULL. The `COALESCE` function takes the result from `CASE` and `price` as its parameters. It returns the first non-NULL value, effectively returning the discounted price if available or the regular price if there’s none.

Final Words

This post has demonstrated various ways to use the `COALESCE` function in your database queries. By evaluating parameters in a specified order and returning the first non-NULL value, the coalesce function simplifies queries making them efficient.

Coalesce is a versatile function whether you are handling null values, string concatenation, data pivoting, validation, or working with computed columns. By mastering the coalesce function, developers can navigate through missing data and create error-free database designs. Remember, to master the technique; you may need some more in-depth practice.

You can now check out how to create foreign key constraints in SQL.