SQL COALESCE Function Usage, Tips & Examples

The SQL COALESCE function is one of the more difficult functions to understand.

Let’s take a look at this function as well as some examples.

Purpose of the SQL COALESCE Function

The SQL COALESCE function aims to return a non-NULL value. It is supplied with a series of values, and returns the first value of those which is not NULL.

It’s great for checking if values are NULL and returning one of the values.

It’s available in Oracle, SQL Server, MySQL, and PostgreSQL.

Let’s take a look at the syntax.

 

SQL COALESCE Syntax

The syntax of the SQL COALESCE function is:

COALESCE ( expr1, expr2, [expr...] )

The syntax is the same for the COALESCE function in Oracle, SQL Server, MySQL, and Postgres.

Let’s look at these parameters in more detail.

Parameters

The parameters of the X function are:

• expr1 (mandatory) – This is the first expression to check for a NULL value. If it is not NULL, then this value is returned.
• expr2 (mandatory) – This is the second expression to check for a NULL value.
• expr… (optional) – Further expressions can be specified, to be checked in case expr1 and expr2 are NULL.

Some things to note for the SQL COALESCE function:

• Only two expressions are needed.
• The first non-NULL expression is returned by the function. This could be expr1, expr2, expr5, or any other expression.
• If all specified expressions are NULL, the function returns NULL. So, it is not a guarantee that no NULL values will be returned.
• The data type returned will be calculated by Oracle based on the expressions.

This function also uses what’s called “short circuit evaluation”. According to Oracle, it means that the expressions are evaluated and determined if they are NULL one at a time, rather than all values being evaluated before checking if any are NULL.

So, if you have a long list of expressions with functions and are worried about the run time for calculating each of the functions for each row, you won’t need to be as worried, because the functions will only calculate if the expressions before them are NULL.

 

Can You Use SQL COALESCE With a Date?

Yes, you can use a DATE value inside COALESCE. It’s treated the same as supplying any other type of expression.

See the examples below for more information on using a date.

 

Can You Use SQL COALESCE With an Empty String?

When using COALESCE, an empty string is treated the same as a NULL value.

So, as you can see in the examples below, the COALESCE statement with an empty string tries to use the next available value.

 

Can You Use SQL COALESCE in a WHERE Clause?

Yes, you can, though it may not be the best way to do it.

The example below shows how to use a COALESCE function in a WHERE clause. However, there are probably better ways to write the statement, depending on how you use it in your application.

 

What’s the Difference between NVL and COALESCE in SQL?

There are several differences between NVL and COALESCE in SQL:

• COALESCE is part of the ANSI-92 standard, where NVL is Oracle-specific
• When using two values, they show the same results
• NVL evaluates both arguments and gives a result. COALESCE evaluates arguments as needed, and is usually faster
• NVL does an implicit conversion to the data type of the first parameter, and COALESCE does not (it will throw an error if they are different)
• NVL can only have two arguments, but COALESCE can have more than 2.

 

Examples of the COALESCE Function

Here are some examples of the Oracle, SQL Server, MySQL, and Postgres COALESCE function.

These examples use the sample customers table that I have used for several other examples.

CREATE TABLE customers (
 first_name varchar2(100),
 last_name varchar2(100),
 country varchar2(20),
 full_address CLOB,
 employees number,
 start_date date
 );

 

The data is then inserted:

INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date)
VALUES ('John', 'Smith', 'USA', '10 Long Road', 4, '12-APR-2010');
 INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date)
VALUES ('Sally', 'Jones', 'USA', '50 Market Street', 10, '04-JUL-2011');
 INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date)
VALUES ('Steve', 'Brown', 'Canada', '201 Flinders Lane', 15, '21-MAR-2009');
 INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date)
VALUES ('Mark', 'Allan', 'UK', '8 Smith Street', 23, '1-FEB-2001');
 INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date)
VALUES ('Adam', 'Cooper', 'USA', '14 Wellington Road', 55, NULL);
 INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date)
VALUES ('Josh', 'Thompson', NULL, '80 Victoria Street', 1, '10-FEB-2012');
 INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date)
VALUES ('Peter', 'Manson', 'France', '5 Johnson St', NULL, '16-OCT-2012');
 INSERT INTO customers (first_name, last_name, country, full_address, employees, start_date)
VALUES (NULL, '', NULL, '155 Long Road', NULL, '16-OCT-2012');

 

The data looks like this:

SELECT * FROM customers;

FIRST_NAME	LAST_NAME	COUNTRY	FULL_ADDRESS	EMPLOYEES	START_DATE
John	Smith	USA	10 Long Road	4	12/APR/10
Sally	Jones	USA	50 Market Street	10	04/JUL/11
Steve	Brown	Canada	201 Flinders Lane	15	21/MAR/09
Mark	Allan	UK	8 Smith Street	23	01/FEN/01
Adam	Cooper	USA	14 Wellington Road	55	(NULL)
Josh	Thompson	(NULL)	80 Victoria Street	1	10/FEB/12
Peter	Manson	France	5 Johnson St	(NULL)	16/OCT/12
(NULL)	(NULL)	(NULL)	155 Long Road	(NULL)	16/OCT/12

Now, let’s take a look at some COALESCE examples.

 

Example 1 – Two Parameters

This query uses two parameters for the COALESCE function

SELECT
first_name,
last_name,
COALESCE(first_name, last_name) AS coal
FROM customers;

Result:

FIRST_NAME	LAST_NAME	COAL
John	Smith	John
Sally	Jones	Sally
Steve	Brown	Steve
Mark	Allan	Mark
Adam	Cooper	Adam
Josh	Thompson	Josh
Peter	Manson	Peter
(NULL)	(NULL)	(NULL)

It shows that all rows display the first_name except for the last one, where NULL is displayed, as there is a NULL value for both parameters.

You might notice that in the INSERT statement above, I inserted an empty string. This is converted to a NULL in the Oracle database.

 

Example 2 – Two Parameters, One is NULL

This example uses two parameters, where the first one may be NULL.

SELECT
country,
last_name, 
COALESCE(country,last_name) AS coal
FROM customers;

Result:

COUNTRY	LAST_NAME	COAL
USA	Smith	USA
USA	Jones	USA
Canada	Brown	Canada
UK	Allan	UK
USA	Cooper	USA
(NULL)	Thompson	Thompson
France	Manson	France
(NULL)	(NULL)	(NULL)

The result is that the country is shown in most instances, but if it is NULL, the last_name is shown.

 

Example 3 – Three Parameters

This query shows COALESCE with three parameters.

SELECT
first_name,
last_name,
country, 
COALESCE(first_name, last_name, country) AS coal
FROM customers
WHERE start_date = '16-OCT-2012';

Result:

FIRST_NAME	LAST_NAME	COUNTRY	COAL
Peter	Manson	France	Peter
(NULL)	(NULL)	(NULL)	(NULL)

You can see that the COALESCE still returns NULL if all parameters are NULL

 

Example 4 – Different Data Types

This query shows the COALESCE function with different data types (VARCHAR, NUMBER, CLOB).

SELECT
first_name,
full_address,
employees,
start_date, 
COALESCE(first_name, full_address, employees, start_date) AS coal
FROM customers;

Result (Oracle):

ORA-00932: inconsistent datatypes: expected CHAR got CLOB
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause: 
*Action:
Error at Line: 41 Column: 78

As you can see, the COALESCE function needs to have the same data types for all parameters, otherwise an error is displayed. It can’t convert the full_address CLOB into a VARCHAR2.

 

Example 5 – Many Parameters

This example uses a lot of parameters for the function.

SELECT
first_name,
last_name, 
COALESCE(
  NULL, NULL, first_name, NULL, NULL, NULL, last_name
) AS coal
FROM customers;

Result:

FIRST_NAME	LAST_NAME	COAL
John	Smith	John
Sally	Jones	Sally
Steve	Brown	Steve
Mark	Allan	Mark
Adam	Cooper	Adam
Josh	Thompson	Josh
Peter	Manson	Peter
(NULL)	(NULL)	(NULL)

It shows the first_name in almost every instance.

 

Example 6 – Empty String

This query uses an empty string as the first parameter.

SELECT
first_name,
COALESCE('', first_name) AS coal
FROM customers;

Result:

FIRST_NAME	COAL
John	John
Sally	Sally
Steve	Steve
Mark	Mark
Adam	Adam
Josh	Josh
Peter	Peter
(NULL)	(NULL)

As you can see, the empty string is treated as a NULL value.

 

Example 7 – Date Parameter

This query uses COALESCE with a date parameter.

SELECT
start_date,
COALESCE(start_date, SYSDATE) AS coal
FROM customers;

Result:

START_DATE	COAL
12-APR-10	12-APR-10
4-JUL-11	4-JUL-11
21-MAR-09	21-MAR-09
1-FEB-01	1-APR-01
(NULL)	16-SEP-22
10-FEB-12	10-FEB-12
16-OCT-12	16-OCT-12
16-OCT-12	16-OCT-12

The result shows the start_date, and when it is NULL, it shows today’s date (which is 16 Sep 2022).

 

Example 8 – COALESCE in WHERE Clause

This query uses a COALESCE in the WHERE clause.

SELECT
country,
LENGTH(country),
employees
FROM customers
WHERE COALESCE(employees, LENGTH(country)) > 4

Result:

COUNTRY	LENGTH(COUNTRY)	EMPLOYEES
USA	3	10
Canada	6	15
UK	2	23
USA	3	55
France	6	(NULL)

The query only shows records where the COALESCE is greater than 4. The COALESCE returns the number of employees, and if that is NULL, it returns the length of the country name.

 

Similar Functions

Some functions which are similar to the COALESCE function are:

• NVL – substitutes a value if a NULL value is found
• DECODE: Translates a value to another value based on certain conditions.
• CASE: This statement performs IF THEN ELSE functionality in Oracle.