FB

Oracle COALESCE FunctionThe Oracle 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 Oracle COALESCE Function

The Oracle 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.

Let’s take a look at the syntax.

 

Oracle COALESCE Syntax

The syntax of the Oracle COALESCE function is:

COALESCE ( expr1, expr2, [expr…] )

Let’s look at these parameters in more detail.

Get Your Free PDF: 9 Ways to Improve your Database Skills

Oracle SQL Function Cheat Sheet

Subscribe and get a PDF cheat sheet of all of the Oracle SQL functions and parameters for easy reference!

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

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 Oracle 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 Oracle 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 Oracle 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 Oracle 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 Oracle?

There are several differences between NVL and COALESCE in Oracle:

  • 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 COALESCE function. I find that examples are the best way for me to learn about code, even with the explanation above.

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 Oracle 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 (VARCHAR2, NUMBER, CLOB).

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

Result:

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-APR-15
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 Apr 2015).

 

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.

You can find a full list of Oracle functions here.

Get Your Free PDF: 9 Ways to Improve your Database Skills

Oracle SQL Function Cheat Sheet

Subscribe and get a PDF cheat sheet of all of the Oracle SQL functions and parameters for easy reference!

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

Image courtesy of digitalart / FreeDigitalPhotos.net

Oracle SQL Function Cheat Sheet

Subscribe and get a PDF cheat sheet of all of the Oracle SQL functions and parameters for easy reference!

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

Get Your SQL Function Cheat Sheet Now: