COALESCE() Function in SQL

When to use COALESCE() Function?

If you wish to retrieve the first non-null value out of a list of expressions, you should use SQL's COALESCE() function. When dealing with situations where you have numerous columns and want to return the first non-null value among them, it is quite helpful. By offering a fallback value in the event that all expressions are null, COALESCE() makes your queries more resilient.

Also read, Natural Join in SQL

Properties of COALESCE() Function in SQL

• Coalesce can contain multiple expressions.
   
• Expressions should be of the same data type.
   
• Coalesce function always executes an integer first followed by a character expression and produces the result in the form of integers.
   

What is a NULL value?

In SQL, NULL is a special marker used to indicate that a data value does not exist in the database. A NULL value can be thought of as an unknown or undefined value.

NULL is not the same as an empty string or a zero value. It represents the absence of a value rather than a value itself.NULL values can occur in any column of a table, and they can be used to represent missing or unknown information. 

For example, the database column could be filled with a NULL value if a customer's id is unavailable.

Examples of COALESCE() Function

Example 1: COALESCE() Function to Replace NULL with a Label

Let's say we want to calculate the total revenue generated by each transaction in the sales table. We can use the COALESCE function to handle null values in the quantity and price_per_unit columns as follows:

SELECT transaction_id, transaction_date, customer_id, product_id,
       COALESCE(quantity, 0) * COALESCE(price_per_unit, 0) AS revenue
FROM sales;

In this example, the COALESCE function is used to replace null values in the quantity and price_per_unit columns with 0 so that the calculation will work even if these values are missing. The * operator is used to multiply the quantity and price_per_unit values together, and the result is assigned an alias of revenue.

The resulting table would look like this:

As you can see, the COALESCE function has replaced null values with 0 in the quantity and price_per_unit columns. It allows us to calculate the revenue for each transaction even if these values are missing.

Example 2: COALESCE() Function When Concatenating NULL and Strings

Let's say we want to create a report of all sales transactions, showing the product name and the name of the salesperson who made the sale. However, the salesperson_id column in the sales table is optional and may contain null values.

We can use the COALESCE function to handle null values in the salesperson_id column and concatenate the salesperson's name with a label of 'N/A' like this:

SELECT transaction_id, CONCAT(product_name, ' - ', COALESCE(salesperson_name, 'N/A')) AS product_and_salesperson
FROM sales
INNER JOIN products ON sales.product_id = products.product_id
LEFT JOIN salespersons ON sales.salesperson_id = salespersons.salesperson_id;

In this example, we are joining the sales table with the products table to get the product name, and then joining the sales table with the salespersons table using a left join to get the salesperson name.

The CONCAT function is used to concatenate the product name and salesperson name (or 'N/A' if no salesperson is assigned), with a dash separator. The COALESCE function is used to replace null values in the salesperson_name column with 'N/A', so that we can still include a label for transactions where no salesperson is assigned.

The resulting table would show the transaction ID and a concatenated string that includes the product name and salesperson name (or 'N/A' if no salesperson is assigned) for each transaction.

In this example, transactions 2 and 4 have no salesperson assigned, so the COALESCE function replaces the null value with 'N/A' in the concatenated string.

Example 3: COALESCE() Function with Multiple Arguments

Let's say we want to create a report of all sales transactions, showing the product name and the salesperson's name if available, but if the salesperson's name is null, we want to display the name of the company's default salesperson.

We can use the COALESCE function to handle null values in the salesperson_name column and provide a default value when it is null, like this:

SELECT transaction_id, product_name, COALESCE(salesperson_name, default_salesperson_name) AS salesperson
FROM sales
INNER JOIN products ON sales.product_id = products.product_id
LEFT JOIN (
    SELECT TOP 1 salesperson_name AS default_salesperson_name
    FROM salespersons
    WHERE is_default = 1
    ORDER BY salesperson_id
) AS default_salesperson ON 1 = 1;

In this example, we are joining the sales table with the products table to get the product name, and then using a left join to a subquery that returns the name of the company's default salesperson. The subquery selects the first salesperson with is_default set to 1 and orders the results by salesperson_id to ensure consistent results.

The COALESCE function is used to replace null values in the salesperson_name column with the name of the company's default salesperson.

The resulting table would show each transaction's transaction ID, product name, and the salesperson's name (or the default salesperson's name if no salesperson is assigned).

In this example, transactions 2 and 4 have no salesperson assigned, so the COALESCE function replaces the null value in the salesperson_name column with the name of the company's default salesperson, which is "Default Salesperson".

Example 4: COALESCE() Function to Replace NULL with a Calculated Value

Suppose we want to create a report of all sales transactions, showing the transaction ID, product name, salesperson name, and commission amount. The commission amount is calculated as 5% of the total sales amount, but if the salesperson is not assigned, we want to display an alternate message, like "No commission".

We can use the COALESCE function to handle null values in the salesperson_name column and provide a default value when it is null, like this:

SELECT transaction_id, product_name, COALESCE(salesperson_name, 'No salesperson') AS salesperson, 
    COALESCE(total_sales_amount * 0.05, 0) AS commission_amount
FROM sales
INNER JOIN products ON sales.product_id = products.product_id;

In this example, we are joining the sales table with the products table to get the product name and using the COALESCE function to replace null values in the salesperson_name column with the message "No salesperson". We also use the COALESCE function to calculate the commission amount as 5% of the total sales amount but replace null values with 0 to avoid errors.

The resulting table would show the transaction ID, product name, salesperson name (or "No salesperson" if no salesperson is assigned), and the commission amount for each transaction.

In this table, you can see that the COALESCE function has replaced null values in the salesperson column with the message "No salesperson", and has calculated the commission amount as 5% of the total sales amount for each transaction.

Example 5: COALESCE() Function with the ROLLUP Clause

The ROLLUP clause in SQL allows you to generate subtotals and grand totals for a group of rows. In combination with the COALESCE function, you can replace null values in the subtotals and grand totals with a default value.

Let's consider the following sales table:

Suppose we want to generate a report that shows the total sales amount for each salesperson, for each sales date, and for the overall total. We can use the ROLLUP clause in combination with the COALESCE function to replace null values with a default values.

Here's an example SQL query that uses the COALESCE function with the ROLLUP clause:

SELECT COALESCE(salesperson, 'Total') as salesperson, COALESCE(CONVERT(varchar, sales_date, 101), 'Total') as sales_date, SUM(sales_amount) as total_sales
FROM sales
GROUP BY salesperson, sales_date WITH ROLLUP

In this query, the COALESCE function is used to replace null values in the salesperson column with the string "Total", and to replace null values in the sales_date column with the string "Total". The ROLLUP clause is used to generate subtotals and grand totals for each combination of salesperson and sales_date.

In this table, you can see that the COALESCE function has replaced null values in the salesperson and sales_date columns with the string "Total". The ROLLUP clause has generated subtotals and grand totals for each combination of salesperson and sales_date.

Coalesce() Function Pivoting

COALESCE() pivoting is a technique used in SQL to pivot data from rows to columns, where the values in the rows are aggregated into a single column using the COALESCE() function. The COALESCE() function combines multiple data columns into a single column, allowing us to create a pivot table where the aggregated data is displayed in columns instead of rows. 

This technique is particularly useful for summarising data from large tables or datasets and can be used to create reports or visualisations that are easier to read and understand.

Data Validation Using SQL Coalesce() Function

SQL COALESCE() function can be used for data validation by replacing NULL values with default or expected values. This helps to ensure that the data is accurate and consistent across the database.

For example, suppose we have a database table containing customer information. The table has columns for the customer's name, email address, and phone number. However, some records are missing email addresses or phone numbers, resulting in NULL values.

We can use the SQL COALESCE() function to replace NULL values with default values or placeholders such as 'Not Available' or 'Unknown', thereby ensuring that all records have complete and consistent data. This can be done as follows:

SELECT name, COALESCE(email, 'Not Available') AS email, COALESCE(phone, 'Unknown') AS phone
FROM customers;

This query will replace any NULL values in the email and phone columns with the specified default values, thereby ensuring that all records have complete and consistent data.

Frequently Asked Questions

What does COALESCE() Function do? 

COALESCE() function can return the first non-NULL expression from the specified list. Some uses of the SQL COALESCE function are: handling NULL values, execute two or more queries as one query, and shortcuts for long and time-consuming CASE statements.

What is the difference between COALESCE() and NULL in SQL?

Coalesce is a general feature supported by all databases such as MYSQL, SQL Server (2008 and newer), Azure SQL Database, PostgreSQL, and Oracle. In SQL, NULL is a special marker that indicates that a data value does not exist in the database.

Is COALESCE() faster than NULL?

The NULL function is typically computationally faster than the COALESCE() expression (although the difference is pretty low) Is NULL function is evaluated once, while the input values for the COALESCE()  expression can be evaluated multiple times.

Why is it called COALESCE?

In English, Coalesce is the process of fusing individual components into a larger whole. The Coalesce() SQL function always returns the first non-null value from the parameter list after evaluating the arguments in the prescribed sequence.

Conclusion

This article extensively discussed the introduction to COALESCE()  function, syntax, parameters, examples, case expression, and coalesce vs is null.

After reading about coalesce in SQL, are you not feeling excited to read/explore more articles on the topic of SQL? Don't worry; Coding Ninjas has you covered. If you want to check out articles related to SQL, you can refer to these links -, 

• SQL VS MYSQL
• MYSQL VS POSTGRESQL
• Introduction to NOSQL
   

Happy Learning!