About the SQL COALESCE () function

As technology grows and evolves, it’s important as a developer to stay up to date with the latest trends. Whether you’re a novice or an expert, a solid understanding of string manipulation helps you prepare data (for example, to generate a different form than an existing one for your business to use) and use built-in SQL server functions. It helps you manage your data using

In addition to data manipulation, you can explore data sets, evaluate data values, and encode or decode them to obtain more meaningful data. As a result, this helps you navigate missing values ​​in your data set, understand their impact on calculations, and streamline the overall work of your data processes to avoid null values ​​that can spoil the results of your operations. It will help you avoid it.

This guide describes SQL join functions that are useful for building complex programs. This post assumes that you have encountered and worked with SQL and would like to deepen your understanding of this particular function. Our series of SQL guides will help you get started quickly.

What is COALESCE () in SQL and its use?

SQL join functions evaluate the parameters (arguments) in the order specified, similar to a list, and return the first non-null value. Simply put, this function evaluates the list in order and terminates at the first non-null value instance. If all arguments in the list are null, the function returns NULL.

Additionally, this function is comprehensive and is also supported by other databases such as MYSQL, Azure SQL Database, Oracle, and PostgreSQL.

You can use Coalesce in the following cases:

• Handling NULL values.
• Run multiple queries as one.
• Avoid long and time-consuming CASE statements.

When used in place of the CASE statement (or ISNULL function), Coalesce takes many parameters, while CASE takes only two. This approach reduces the amount of code you write and eases the creation process.

The syntax is:

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

Coalesce in SQL Server has several properties, including arguments of the same data type, accepts many parameters, and arguments of integer type that can be cascaded by the yield function to return an integer as output.

Also read: The ultimate SQL cheat sheet to bookmark for later

But before we get into how to use Coalesce, let’s understand what NULL is.

What is a NULL value in SQL?

The SQL unique marker NULL indicates that the value does not exist in the database. This can be thought of as an undefined or unknown value. Avoid falling into the trap of thinking of this as an empty string or zero value. It’s a lack of value. The appearance of NULL in a table column represents missing information.

In a real-world use case, if the customer did not provide an ID, you could enter a NULL value in the data column of a database column on an e-commerce website. Null in SQL is unique. This is a state, as opposed to other programming languages, where it means ” not pointing to a specific object “.

NULL values ​​in SQL have a significant impact on relational databases. First, you can exclude certain values ​​when working with other internal functions. For example, you can generate a list of total orders in production, but others still need to be completed. Using NULL as a placeholder allows the internal SUM function to add the sum.

Additionally, consider the case where you need to generate an average using the AVG function. Using zero values ​​will skew the results. Instead, the database can remove such fields and use NULL to get accurate output.

There are no disadvantages to NULL values. These are considered variable length values ​​of bytes or multiples. If you exceed the number of bytes stored in the database, the database leaves space for these bytes, so the database takes up more space on your hard drive compared to using the normal value. It will be.

Additionally, when using some functions, you must customize the functions to exclude NULLs. As a result, SQL procedures become longer.

Handling NULL values ​​with COALESCE ()

A null value means that you can get the value, but you don’t know what that value should be. Null values ​​are guaranteed until you collect data to populate the field with an actual value.

Although you can use NULL values ​​for several data types in your database (such as decimals, strings, BLOBs, and integers), we recommend that you avoid using NULL values ​​when working with numeric data.

The disadvantage is that when used with numbers, it can require some explanation when developing code that manipulates the data. More on that later.

Different ways to handle NULL values ​​using COALESCE ():

Replace null values ​​with specific values ​​using COALESCE ()

COALESCE() allows you to return a specific value for all null values. For example, suppose a table named “employees” has a “salary” column. This column may contain NULL values ​​if the employee’s salary has not been deposited. Therefore, when doing calculations, you may want to use a specific value (in this case zero) for all NULL entries. Here’s how:

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

Select first non-null value from multiple options using COALESCE ()

In some cases, you may want to operate on the first non-null value in a list of expressions. In such cases, there are often multiple columns containing related data, and you want to prioritize their non-null values. The syntax remains.

 COALESCE (expression1, expression2, …)

In a real case, let’s say we have a contacts table with preferred_name and full_name columns. You can also generate a list by contact’s preferred name (if available) or full name. Here’s what to do:

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

In this test case, if preferred_name is not NULL, it is returned. Otherwise, it is returned as full-name display name.

String concatenation with SQL Coalesce

NULL values ​​can cause problems in SQL when concatenating strings. In such cases, NULL is returned as an undesired result. Since NULL is not a desired result, you can use the Coalesce function to solve the problem. For example:

Simple string concatenation is done like this:

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

The code returns:

However, if you use NULL values ​​as shown below:

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

The output is now:

The above result is NULL because all text string concatenations involving NULL values ​​return NULL. However, this problem is solved using coalesce () . This function returns an empty string (or space) instead of NULL. For example, suppose you are listing the names of cars and their manufacturers. This is your question.

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

If the manufacturer is NULL, a “-” is displayed instead of NULL. The expected results are:

As you can see, NULL results are removed using the option to insert a replacement string value.

SQL Coalesce functions and pivots

SQL pivoting is a technique used to transform rows into columns. You can transpose (rotate) data from a “normalized” format (more rows and fewer columns) to a “denormalized” format (fewer rows and more columns). The Coalesce function can be used with SQL pivots to handle NULL values ​​in pivoted results.

PIVOT in SQL converts rows into columns. The resulting column is an aggregation function on some data. In any case, if the aggregation result for a particular cell is null, you can use ` COALESCE ` to replace the null value with a default value or a meaningful representation. For example:

Suppose you have a table sales with columns called year , quarter , and revenue and you want to pivot the data. For example, the column displays the year and the value displays the sum of each quarter’s revenue. However, some quarters do not have revenue data and will display null values ​​in the pivoted results. In this case, you can use COALESCE to replace NULL values ​​in the pivoted result with 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 functions and SQL join functions

You can use scalar UDFs and coalescing to perform complex logic to handle NULL values. Together, these features enable SQL queries to perform more sophisticated data transformations and calculations. 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 may want to calculate each employee’s total income (salary and bonus). However, there are some missing values. In this case, the scalar UDF can handle addition of salary and bonus, and Coalesce handles NULL values. The scalar UDF for total revenue is:

 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 need to validate numbers. For example, suppose table products has columns product_name , price , and discount . Let’s say I want to get the product name, price, and discount for each item. However, if you want to treat all NULL discount values ​​as 0, the Coalesce function is useful. Here’s how to use it:

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

SQL joins and computed columns

A computed column is a virtual column that is calculated based on a formula or other columns in the table. Because computed columns are not physically stored in the database, you can use join functions to leverage computed columns when processing complex scenarios and transformations. Here is a practical example.

Consider a ` `product ” table that contains columns “ price ”, “ discount ”, and “ tax_rate ”. In this case, you want to create a calculated column ` total_price ` that represents the final product price after applying discounts and taxes. If no discount or tax is specified (NULL), we recommend using zero and continuing the calculation. We will introduce how to utilize coalescence according to your 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, the following happens:

1. total_price calculation column is defined as (COALESCE(price, 0) – COALESCE(price*discount, 0))* COALESCE(1+tax_rate, 1) .
2. If price is NULL, COALESCE(price*discount, 0) price is treated as 0.
3. If discount is null , COALESCE(price*discount) discount is guaranteed to be treated as 0, and the multiplication has no effect on the calculation.
4. If tax_rate is NULL, COALESCE(1 + tax_rate, 1) ensures that no tax is applied and the multiplication is treated as 0, meaning it has no effect on the calculation.

The above configuration allows us to generate a calculated column total_price that contains the actual final price even if there are missing or NULL values.

SQL joins and CASE expressions

You can use joins syntactically through CASE expressions. For 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

With the above configuration, CASE query is similar to the COALESCE function.

Additionally, it is also possible to use COALESCE and CASE expressions within the same query. These two techniques allow you to handle null values ​​and apply conditional logic at the same time. Let’s explain this with an example.

Consider you have a table products with product_id , product_name , price , and discount . Some products have special discounts, while others do not. If the product has a discount, you should display the discounted price, otherwise you should display the regular price.

 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, ` CASE ` checks if ` discount ` is greater than 0 and calculates the discounted price. Otherwise returns NULL. The ` COALESCE ` function takes the results from ` CASE ` and ` price ` as parameters. Returns the first non-null value, effectively returning the discounted price if available, or the regular price if not.

last word

In this post, we have explained various ways to use the ` COALESCE ` function in database queries. By evaluating parameters in the specified order and returning the first non-null value, the Coalesce function simplifies and makes queries more efficient.

Coalesce is a versatile function, whether it handles null values, concatenating strings, pivoting data, validating, or manipulating calculated columns. Mastering join functionality allows developers to navigate missing data and create error-free database designs. Don’t forget to master the technique. Further practice may be required.

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