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:
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:
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.
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.
You can also use the Split function to divide the CSV into rows in the following way:
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”:
2. Fill up the “sales” table with data:
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.
4. To get around this, we may use COALESCE to force NULL values to return 0 in this scenario.
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:
There must be at least two expressions passed.
Example
Here’s an example to show what we mean:
Here’s another illustration:
The output will be seen as this:
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 requires a simple statement such as this:
