Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
COALESCE() Function Syntax
2.
Parameters of COALESCE() Function 
3.
When to use COALESCE() Function?
4.
Properties of COALESCE() Function in SQL
5.
What is a NULL value?
6.
Examples of COALESCE() Function
6.1.
Example 1: COALESCE() Function to Replace NULL with a Label
6.2.
Example 2: COALESCE() Function When Concatenating NULL and Strings
6.3.
Example 3: COALESCE() Function with Multiple Arguments
6.4.
Example 4: COALESCE() Function to Replace NULL with a Calculated Value
6.5.
Example 5: COALESCE() Function with the ROLLUP Clause
7.
Coalesce() Function Pivoting
8.
Data Validation Using SQL Coalesce() Function
9.
Frequently Asked Questions
9.1.
What does COALESCE() Function do? 
9.2.
What is the difference between COALESCE() and NULL in SQL?
9.3.
Is COALESCE() faster than NULL?
9.4.
Why is it called COALESCE?
10.
Conclusion
Last Updated: Mar 29, 2024
Medium

COALESCE() Function in SQL

Author Ashish Sharma
0 upvote
gp-icon
DBMS - Database management systems
Free guided path
12 chapters
93+ problems
gp-badge
Earn badges and level up

COALESCE is a built-in Structured Query Language (SQL) function that handles NULL values in data records. It sequentially examines a set or list of input parameters and returns the first non-NULL value among them. If all of the arguments in the input list are NULL, the function returns NULL.

coalesce in sql

The SQL Server COALESCE() function evaluates its arguments one at a time and returns the first non-NULL value in the specified number of expressions. 

Following are some important points you must know about COALESCE() function in SQL:

  • The expressions in the COALESCE function are evaluated in order from left to right, and the first non-null value is returned.
     
  • The COALESCE function can be used with any data type.
     
  • It's commonly used to replace null values with a default value, such as replacing null email addresses with the string 'N/A'.
     
  • In SQL Server, the ISNULL function can be used as an alternative to COALESCE, but it only accepts two arguments and is less flexible.

COALESCE() Function Syntax

Select Coalesce(value1, value2, value3, ... value n);

Parameters of COALESCE() Function 

The coalesce function accepts only one parameter, a list of distinct values.  

The (value1, value2,….., value n) specifies a list value to return a NON-NULL or NULL value in the output. It can be of any type but must be the same for all expressions.

Let's understand it more clearly by the following cases:

Case 1: Coalesce(null,null) 

Case 2: Coalesce(0,null)

Both test cases will return the null value because the function didn't find any non-null value from the database.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

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.
     
properties of coalesce function

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. Theoperator 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:

transaction_idtransaction_datecustomer_idproduct_idrevenue
12023-01-03100110120.00
22023-02-03100210220.00
32023-03-03100310315.00
42023-04-03100110415.00
52023-05-03100210524.00

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.

transaction_idproduct_and_salesperson
1Product A - Suraj
2Product B - N/A
3Product C - Ayush
4Product D - N/A
5Product E - Ankit

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

transaction_idproduct_namesalesperson
1Product ASuraj
2Product BDefault Salesperson
3Product AAyush
4Product CDefault Salesperson
5Product BAnkit

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:

transaction_idproduct_idquantitytotal_sales_amount
110122000.00
210211000.00
310133000.00
410324000.00
510244000.00

 

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.

transaction_idproduct_namesalespersoncommission_amount
1Product ASuraj100.00
2Product BAyush50.00
3Product AManav150.00
4Product CNo Salesperson200.00
5Product BAnkit200.00

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:

transaction_idproduct_namesales_amountsales_datesalesperson
1Product A1000.002023-01-03Suraj
2Product A2000.002023-01-03Ayush
3Product B1500.002023-02-03Manav
4Product B1200.002023-02-03Ankit
5Product C1800.002023-05-03NULL

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.

salespersonsales_datetotal_sales
Suraj03/01/20232000.00
Ayush03/01/20231000.00
Manav03/02/20231500.00
Ankit03/02/20231200.00
Total03/01/20233000.00
Total03/02/20232700.00
TotalTotal5700.00

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 -, 

Happy Learning! 

Previous article
Injection
Next article
SQL Problems
Guided path
Free
gridgp-icon
DBMS - Database management systems
12 chapters
93+ Problems
gp-badge
Earn badges and level up
Live masterclass