What is SQL COALESCE function?

COALESCE is an embedded function in MS SQL Server, MySQL, PostgreSQL, and a few other databases that makes it easier for you handle NULL values effectively. NULL is a difficult concept in SQL. When a NULL value is used in an expression, it always produces a NULL result.

What is the purpose of the COALESCE function?

The COALESCE function in SQL returns the very first non-null result from the list of parameters supplied to it.

The SQL COALESCE function is really similar to the NVL function, with the exception that SQL COALESCE can take many parameters rather than the one argument used by NVL.

Although the SQL COALESCE function can be used to substitute NVL functions, it is recommended that single parameter values be handled by NVL.

The syntax is as follows:

SQL COALESCE

The number of parameters that can be supplied to the SQL COALESCE function is unlimited. The SQL COALESCE function will definitely return NULL as a result if all of the parameters given are NULL.

Caveats of using the COALESCE function

A few remarks on the COALESCE function:

  • Non-NULL values are checked for in the arguments.
  • The datatypes for all of the parameters should be the same. Otherwise, there will be an error.
  • The COALESCE function can take as many parameters as it wants.
  • The arguments are presented from left to right.
  • Comparable to NVL and IFNULL, which are available in various DBMS, the Coalesce function provides similar compatibility.
  • Although CASE and COALESCE may be used for identical tasks, COALESCE has the benefit of requiring significantly less typing.
  • When the first non-NULL value is reached, it will cease considering arguments.

Example

If both columns in the staff table are NULL, use the SQL COALESCE function to return date hired, DOB, or sysdate:

SQL COALESCE function

Let’s use another example for a clearer understanding:

The Nickname is taken from the Persons table in the example above. If it is NULL, it is passed on to FirstName. If “Mr./Mrs.” is also NULL, “Mr./Mrs.” is used. Finally, it includes the surname. Overall, it strives to make the most use of the information supplied to create a complete name that is as casual as feasible. This approach only works if the nicknames and first names that are missing are truly NULL; if either of them is an empty string therefore, COALESCE will gladly return it to the caller.

Using the COALESCE function in SQL Server to retrieve comma (delimited) separated values from a table

In SQL Servers 2005 to 2014, the COALESCE function may be used to retrieve CSVs (delimited) from a Table.

The below Stored Procedure accepts the city (input variable) and EmployeeIds (output variable) arguments (Output parameter).

Based on the City, it returns the ID of all Employees. Using SQL Server’s COALESCE function, the resulting Employee Ids are separated (delimited) by commas.

Using SQL Server's COALESCE function

The next step is to get the Stored Procedure’s CSV (delimited) returned values. To get the CSV data from the Procedure that has already been stored, create a variable with an identical data type as well as the same size as the parameter providing the output, then use the OUTPUT keyword to send it as the Output parameter.

Stored Procedure's CSV

You can also use the Split function to divide the CSV into rows in the following way:

 Split function

How to prevent NULL values with COALESCE function

The COALESCE function is useful for preventing NULL values from showing in query results.

Let’s look at an example by following the steps below:

1. Using the CREATE TABLE command, create a table called “sales”:

CREATE TABLE command

2. Fill up the “sales” table with data:

 COALESCE function CREATE TABLE command

3. If we try to subtract a value from the “discount” column from a value from the “amount” column, we get a NULL value in the last row. This is due to the fact that any arithmetic on a NULL value would return NULL.

 COALESCE function NULL

4. To get around this, we may use COALESCE to force NULL values to return 0 in this scenario.

COALESCE to force NULL

COALESCE() Function in Oracle

The COALESCE() function in Oracle Database returns the very first non-null expression in the expression list.

The syntax is as follows:

COALESCE() Function in Oracle

There must be at least two expressions passed.

Example

Here’s an example to show what we mean:

COALESCE() Function in Oracle example

Here’s another illustration:

COALESCE() Function in Oracle example

The output will be seen as this:

COALESCE() Function in Oracle output

When all of the parameters are null, COALESCE() returns null. We added SET NULL ‘null’; to the first line just so the SQLcl session returns null whenever a result is null.

When null happens as a consequence of an SQL SELECT statement, SQLcl and SQL*Plus return blank by default. SET NULL, on the other hand, can be used to define a specific string that will be returned. We asked for the string null to be returned in this case.

When should you utilize the COALESCE function, and how should you use it?

In actuality, COALESCE is often used to shorten a CASE statement that requires evaluating each item in a list to locate the first value that’s not null. Considering a list of n values, the SQL statement using CASE will look something like this:

COALESCE shorten a CASE statement

COALESCE requires a simple statement such as this:

COALESCE statement

Leave a Comment