coalesce

Return the first non-null value in a list of arguments.

For an overview see page eXtremeSQL Functions

Prototype

     
    coalesce( arg1, arg2, ... argN);
     

Arguments

arg1, arg2, ... argN

A list each element of which can be a constant value, an array, a sequence, time or a table column of any of these types

Description

This function returns the first non-null value in a list of arguments. The list is composed of elements each of which can be a constant value, an array, a sequence, time, or a table column of any of these types. This can be useful when a result is required but it is not know when a field value is present.

For example, consider a table Person with three types of contact phone number: Business_Phone, Cell_Phone and Home_Phone.

If we want to get a contact number for each person we might specify the following select statement to extract the first non-null value:

     
    select Name, coalesce( Business_Phone, Cell_Phone, Home_Phone) as Contact_Phone
    from Person;
     

( See the example 3 below.)

Example 1

The coalesce() function can be used with simple lists such as the following:

 
    select coalesce( 1, 2, 3), coalesce( null, null, 3);
    #1      #2
    --------------------------------------------------------
    1       3
     

Example 2

Or it can be used with table columns of different types. For example, consider the table t below initialized with the following two rows of values:

 
    create table t(
        i1 int, i2 int,
        ch1 char(20), ch2 char(20),
        sc1 sequence(char(5)), sc2 sequence(char(5)),
        si1 sequence(int), si2 sequence(int),
        a1 array(int), a2 array(int),
        t1 time, t2 time,
        d1 decimal(15,2), d2 decimal(15,2),
        b1 boolean, b2 boolean
    );
    insert into t values(null, 1, 
                null, 'ch1', 
                null, ['1','2','3','4','5'], 
                null, [1,2,3,4,5], 
                null, [1,2,3,4,5,6,7,8,9,0], 
                null, '04-02-2019 10:19', 
                null, 10.2,
                null, true);
    insert into t values(2, null, 
                'ch2', null, 
                ['a1','a2','a3','a4','a5'], null,
                [11,12,13,14,15], null,
                [10,20,30,40,50], null,
                '04-02-2019 10:23', null,
                10.02, null,
                null, false);
                 

The following select statement uses function coalesce() to extract the first non-null value from each of the specified lists of fields (note that the select results are reformatted here to make it easier to see how they correlate with the fields):

     
    select coalesce(i1, i2) as i, 
        coalesce(ch1, ch2) as ch, 
        coalesce(sc1, sc2) as sc, 
        coalesce(si1, si2) as si, 
         coalesce(a1, a2) as a,
        coalesce(t1, t2) as t,
        coalesce(d1, d2) as d,
        coalesce(b1, b2) as b 
         from t order by i;
 
        i 
          ch      
    sc      
    si 
         a 
          t 
          d 
          b
    --------------------------------------------------------
    1 
          ch1 
        {1, 2, 3, 4, 5} 
    {1, 2, 3, 4, 5} 
    [1, 2, 3, 4, 5, 6, 7, 8, 9, 0]  
    04/02/2019 10:19:00 
        10.20
    true
     
    2       
    ch2 
        {a1, a2, a3, a4, a5} 
       {11, 12, 13, 14, 15} 
       [10, 20, 30, 40, 50]    
    04/02/2019 10:23:00
    10.02 
      false
 

Example 3

The practical example outlined in the description above could use a table Person defined and initialized as follows:

 
    create table Person( Name char(20), Business_Phone char(20), 
                Cell_Phone char(20), Home_Phone char(20));
    insert into Person values( 'Hal', '888-555-9999', '888-555-1111', '888-555-3333');
    insert into Person values( 'Barb', null, '888-555-2222', '888-555-4444');
    insert into Person values( 'Doug', null, null, '888-555-5555');
 
    select * from Person;
    Name    Business_Phone  Cell_Phone      Home_Phone
    --------------------------------------------------------
    Hal     888-555-9999    888-555-1111    888-555-3333
    Barb    null    888-555-2222    888-555-4444
    Doug    null    null    888-555-5555
     

Now we could use the following select to extract a contact number for each table row with the following rules:

1. If a person has a business phone, use the business phone number.

2. If a person does not have a business phone and has a cell phone, use the cell phone number.

3. If a person does not have a business phone, does not have a cell phone, and has a home phone, use the home phone number.

 
    select Name, coalesce( Business_Phone, Cell_Phone, Home_Phone) as Contact_Phone
    from Person;
 
    Name    Contact_Phone
    --------------------------------------------------------
    Hal     888-555-9999
    Barb    888-555-2222
    Doug    888-555-5555