SQL COALESCE

Introduction to SQL COALESCE

COALESCE is a predefined built-in Structured Query Language(SQL) function that is used to handle NULL values in the data records. It evaluates a set or list of input parameters in a sequential manner and returns the first non NULL values among them. The COALESCE function terminates once it encounters the first non NULL value and returns it. If all the arguments in the input list are NULL values, then the function will return NULL.

It is a generic function that is supported in all databases like MYSQL, SQL Server(starting 2008), Azure SQL database, PostgreSQL, Oracle, etc.

Some uses of SQL COALESCE function are :

• Handling NULL values
• Running two or more queries as a single query
• Shortcut alternative to lengthy and time-consuming CASE statements

Syntax and Parameters

The basic syntax for using COALESCE function in SQL is as follows:

SELECT COALESCE(value_1, value_2,value_3,value_4, …value_n);

The parameters mentioned in the above syntax are :

COALESCE() : SQL function that returns the first non-null value from the input list.

value_1, value_2,value_3,value_4, …value_n : The input values that have to be evaluated.

Since COALESCE function is an expression in itself, it can be used in any statement or clause that accepts expressions like SELECT, WHERE and HAVING.

COALESCE function can be considered a specialized version of the CASE statement in SQL.

The syntax of the CASE statement showcasing the function of COALESCE function is as follows :

SELECT column_name1,column_name2, column_name3,
CASE
WHEN column_name1 IS NOT NULL THEN column_name1
WHEN column_name2 IS NOT NULL THEN column_name2 ELSE NULL
END as 'case_name'
FROM table_name;

The parameters mentioned in the above syntax are :

Column_name1, column_name2: The values of columns that have to have coalesced, i.e. if the results of any of these columns result in NULL values, then the first non-NULL value among them will be returned. But if both of them evaluates to NULL, then CASE will return NULL.

Going ahead we will be discussing the above mentioned COALESCE function in great detail.

In order to understand the concept better, we will take the help of the employee’s table( this contains personal details of all the employees).

The data in the “employees” table is as follows :

Examples of SQL COALESCE

Here are a few examples to understand SQL COALESCE better.

Example #1

SQL query to illustrate the use of simple COALESCE function.

Code:

SELECT COALESCE(NULL, NULL, NULL, 'EduCBA', NULL, NULL);

Output:

In this example, we can see that the COALESCE function selects the first not NULL value, it encounters and then terminates.

Example #2

SQL query to find the first non-null values from an employee’s first name and last name.

Code:

SELECT employeeid,firstname, lastname,
COALESCE(firstname,lastname) as 'first not null name'
FROM employees;

Output:

In this example, the first non NULL is returned.

Example #3

SQL query to illustrate COALESCE function as a special case of CASE.

Code:

SELECT employeeid,firstname, lastname,
CASE
WHEN firstname IS NOT NULL THEN firstname
WHEN lastname IS NOT NULL THEN lastname ELSE NULL
END as 'first not null value'
FROM employees;

Output:

Compare the results of this query and that of the query in the third example. We can see that both of them produce the same result. This is because the COALESCE function can be considered as a special case of CASE.

Example #4 

SQL query to illustrate the use of COALESCE function on values with different data types.

Code:

SELECT employeeid,firstname, lastname,
COALESCE(employeeid,firstname,lastname) as 'first not null name'
FROM employees;

Output:

In the above example, we can see that employee id could not be clubbed with first name and last name data values as they have different data types.

In order to solve this problem, we may change the data type of employeeid using the CONVERT function as shown below.

Code:

SELECT employeeid,firstname, lastname,
COALESCE(CONVERT(varchar(50),employeeid),firstname,lastname) as 'first not null values'
FROM employees;

Output:

Example #5 

A complex SQL query to illustrate the use of COALESCE function.

Suppose we want to know the salaries of all the employees in the firm. But in the employee’s table, we can see that the salaries of all employees have not been mentioned. However, hourly rates and commission of employees with missing salary fields have been mentioned. We can use that to compare employee salaries in the following manner.

Code:

SELECT COALESCE(CONVERT(varchar(50),employeeid),firstname,lastname) as 'Employee Identifier',
COALESCE(salary,hourly_rate*8*30 + COALESCE(commission,0))as 'Compensation'
FROM employees
ORDER BY 2 DESC;

Output:

Using the first COALESCE function, we tried to identify an employee by coalescing employeeid, first name, and last name together and then using the second COALESCE function, we tried to select salary or calculated compensation.

Conclusion

SQL COALESCE function is a built-in function in databases like SQL Server, Azure, etc. that returns the first non NULL value from an input list of arguments. It is very useful in handling null values and serves a quick and easy alternative to SQL CASE statements.

Recommended Articles

This is a guide to SQL COALESCE. Here we discuss the few examples to understand SQL COALESCE along with the syntax and parameters. You may also have a look at the following articles to learn more –

1. SQL Subquery
2. SQL with Clause
3. SQL Arithmetic Operators
4. Table in MySQL