Geekflare is supported by our audience. We may earn affiliate commissions from buying links on this site.
In Database Last updated: September 4, 2023
Share on:
Invicti Web Application Security Scanner – the only solution that delivers automatic verification of vulnerabilities with Proof-Based Scanning™.

With technology growing and evolving, it’s essential to stay updated with the latest trends as a developer. Whether a beginner or an expert, a solid understanding of string manipulation helps you prepare data (for instance, generating a different form from the existing one, making it usable for your business) and manage it using inbuilt SQL server functions.

Besides data manipulation, you can examine data sets, evaluate data values, and encode or decode them to drive more meaningful data. As a result, this helps you navigate through missing values in data sets, comprehend their impact on calculations, and streamline the overall working with the data process to avoid Null values which may ruin operation results.

This guide walks you through the coalesce function in SQL, which helps build complex programs. The post assumes you’ve encountered and worked with SQL and just looking to reinforce your understanding of this particular function. Our series of SQL guides can help you get started quickly.

What is COALESCE () in SQL and Its Uses?

The coalesce function in SQL evaluates parameters (arguments) in a specified order, like lists, and returns the first non-null value. Simply put, the function assesses your list sequentially and terminates at the instance of the first non-null value. If all arguments in the list are null, the function returns NULL.

Additionally, the function is inclusive and supported in other databases like MYSQL, Azure SQL Database, Oracle, and PostgreSQL.

You can use Coalesce in the following instances when:

  • Handling NULL values.
  • Running several queries as one.
  • Avoiding lengthy, time-consuming CASE statements.

When used in place of CASE statements (or the ISNULL function), coalesce takes many parameters, unlike the CASE, which only takes two. This approach lets you write less code and eases the writing process.

Here’s the syntax:

COALESCE(valueOne, valueTwo, valueThree, …, valueX);

Coalesce in the SQL server has several properties, including arguments of the same datatype, accepting many parameters, and arguments of the integer type to be cascaded by a yield function to return an integer as the output.

Also read: Ultimate SQL Cheat Sheet to Bookmark for Later

But before getting into how to use coalesce, let’s understand NULL.

What is a NULL Value in SQL?

The unique marker NULL in SQL indicates the inexistence of a value in the database. You can think of it as an undefined or unknown value. Please don’t get into the pitfall of thinking about it as an empty string or a zero value; it’s the absence of a value. Null occurrence in table columns represents missing information.

In a practical use case, the data column in an e-commerce website database column can be filled with a NULL value if a customer fails to provide their id. Null in SQL is unique; it is a state, as opposed to other programming languages where it means “not pointing to a particular object“.

The NULL values in SQL have a significant impact on relational databases. First, they allow you to exclude particular values while working with other internal functions. For example, you can generate a list of total orders in a production environment, but others still need to be completed. Using NULL as your placeholder allows the internal SUM function to add the totals.

Additionally, consider cases when you need to generate the average using the AVG function. If you work with zero values, the results are skewed. Instead, the database can remove such fields and use NULL, resulting in accurate outputs.

NULL values are not with drawbacks. They are considered variable-length values, being bytes or several of them. As the database leaves room for these bytes if they exceed what is stored in the database, the result is that your database takes more space on the hard drive as opposed to using regular values.

Additionally, when working with some functions, you’ll need to customize them to eliminate NULLS. This, as a result, makes your SQL procedures longer.

Handling NULL Values with COALESCE ()

Null values imply that you could have a value, but you’re not aware of what the value should be. Until you collect data that fills your fields with real values, NULL values are the procurators.

While you can use NULL values for multiple data types in your database, including decimals, strings, blobs, and integers, it’s a good practice to avoid them when dealing with numerical data.

The drawback is that when used for numeric values, you’ll likely need clarification as you develop the code that works with data. More about that later.

The different ways COALESCE () can be used to handle the NULL value:

Using COALESCE () to Replace Null Values With a Specific Value

You can use COALESCE () to return specific values for all null values. For instance, you may have a table called “employees” with a column “salary”, which may contain null values if employees’ salary has not been credited. So, when doing some calculations, you may want to work with a specific value, zero in this case, for all NULL entries. Here’s how to do it.

SELECT COALESCE(salary, 0) AS adjusted_salary
FROM employees;

Using COALESCE () to Select the First Non-null Value From Multiple Options

Sometimes, you may want to work with the first non-NULL values in a list of expressions. In such cases, you often have multiple columns with related data, and you’d want to prioritize their non-NULL values. The syntax remains.

COALESCE (expression1, expression2, …)

In a practical case, suppose you have a contacts table with the columns preferred_name and full_name. And you’d want to generate a list of contacts side by side with their preferred names (if available) or their full names. Here’s how to tackle it.

SELECT COALESCE(preferred_name, full_name) AS display_name
FROM contacts.

If the preferred_name is not NULL for this test case, it will be returned. Otherwise, the full-name is returned as the display name.

String Concatenation with SQL Coalesce

You may encounter issues with SQL when concatenating strings if null values are involved. In such instances, NULL is returned as an undesirable result. Now that NULL is not our desired outcome, you can fix the issue using the coalesce function. Below is an example.

A simple string concatenation is done by:

SELECT ‘Hello, where are you, ‘|| ‘John ’||? AS example

The code returns:

Hello, where are you, John?

However, if you use a NULL value, as shown below:

SELECT ‘Hello, where are you, ‘ || null || ‘?’ AS example

The output is now.


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.

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.

outlander, manufacturer: —
flying spurs, manufacturer: Bentley
royal athlete, manufacturer: —
royal saloon, manufacturer: Crown

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).

    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 (
    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)
    DECLARE @totalEarnings INT;
    SET @totalEarnings = @salary + COALESCE(@bonus, 0);
    RETURN @totalEarnings;
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:

Productname + ‘ ’+ deliverydate productdetails,
WHEN cellphone is NOT NULL Then cellphone
WHEN workphone is NOT NULL Then workphone

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.

            WHEN discount > 0 THEN price - (price * discount / 100)
            ELSE NULL
    ) 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.

  • John Walter
    John Walter is an Electrical and Electronics Engineer with deep passion for software development, and blockchain technology. He loves to learn new technologies and educate the online community about them. He is also a classical organist.
Thanks to our Sponsors
More great readings on Database
Power Your Business
Some of the tools and services to help your business grow.
  • Invicti uses the Proof-Based Scanning™ to automatically verify the identified vulnerabilities and generate actionable results within just hours.
    Try Invicti
  • Web scraping, residential proxy, proxy manager, web unlocker, search engine crawler, and all you need to collect web data.
    Try Brightdata
  • is an all-in-one work OS to help you manage projects, tasks, work, sales, CRM, operations, workflows, and more.
    Try Monday
  • Intruder is an online vulnerability scanner that finds cyber security weaknesses in your infrastructure, to avoid costly data breaches.
    Try Intruder