website logo
⌘K
🏠Homepage
πŸ‘‹Introduction
Key Concepts & Architecture
πŸš€Run Oxla in 2 minutes
πŸƒβ€β™‚οΈRun Oxla on S3
πŸ›«Multi-node Deployment
πŸ‘¨β€πŸ’»SQL Reference
SQL Queries
SQL Clauses
SQL Data Types
SQL Functions
Schema
Comment Support
🚨Error Handling
πŸ†šDifferences Between Oxla vs. PostgreSQL
πŸ“ˆPublic Metrics
Docs powered by
Archbee
SQL Reference
...
SQL Functions
Other Functions

COALESCE

21min

Overview

A table can hold empty (null) and filled (non-null) values. Yet, often, we prefer to overlook those empty values. This is where COALESCE() steps in.

COALESCE() function helps us when we want to ignore null values while processing data. It returns the first argument that is not null, while the remaining arguments from the first non-null argument are not evaluated.

If all arguments are null, the COALESCE function will return null.

Syntax

The syntax for the COALESCE() function is as follows:

Pgsql
|
COALESCE (argument_1, argument_2, …);
ο»Ώ

Key points from the syntax:

  • COALESCE() requires a minimum of two inputs.
  • It can take an unlimited number of arguments.
  • Evaluation occurs sequentially from left to right, stopping at the first non-null value.

Examples

Here are some examples to illustrate the application of COALESCE():

Case #1: Returning the First Non-Null Value

In this example, we have a set of values. By using the COALESCE() function, we're going to get the first non-null value from this set.

Pgsql
|
SELECT COALESCE(9, 3, 8, 7, 1);
ο»Ώ

The result will be 9, the first value without null among the provided options.

Pgsql
|
 coalesce 
----------
        9
ο»Ώ

Case #2: Handling NULL Value as the Last Argument

Let’s include NULL as the final argument and check the query output.

Pgsql
|
Select COALESCE(3,4,5,9,10,NULL);
ο»Ώ

The function output is 3 because it returns the first non-null value.

Pgsql
|
 coalesce 
----------
        3
ο»Ώ

Case #3: Handling NULL Value as the First Argument

Consider NULL as the first argument in the following example.

Pgsql
|
Select COALESCE(NULL,1,5,7,9,2);
ο»Ώ

The output is 1, as it is the first non-null value of the argument.

Pgsql
|
 coalesce 
----------
        1
ο»Ώ

Case #4: Handling Multiple NULL Values

In the following query, NULL appears in the first, second, fourth, and last positions.

Pgsql
|
Select COALESCE(NULL, NULL ,3, NULL, 7,9,4,5, NULL);
ο»Ώ

The COALESCE() function ignores the first two NULLs and returns the first non-null value, 3. It does not process the subsequent NULL values.

Pgsql
|
 coalesce 
----------
        3
ο»Ώ

Case #5: Handling All NULL Values

Assume that the given values are entirely composed of nulls.

Pgsql
|
Select COALESCE(NULL, NULL ,NULL, NULL);
ο»Ώ

In this case, the COALESCE() function returns an empty value (null).

Pgsql
|
 coalesce 
----------
 
ο»Ώ

Case #6: COALESCE() with Table Data

Imagine we have the employee_absent table, which comprises a mix of NULL and non-null values:

Pgsql
|
CREATE TABLE employee_absent (
    emp_name TEXT,
    emp_dept TEXT,
    absent TEXT
);

INSERT INTO employee_absent (emp_name, emp_dept, absent)
VALUES
    ('Alice', 'Finance', 'absent'),
    ('Bob', 'Operations', 'absent'),
    ('Carol', 'Finance', 'absent'),
    ('David', 'HR', NULL),
    ('Emily', 'HR', NULL);
ο»Ώ

Use the SELECT statement to display all the records:

Pgsql
|
SELECT * FROM employee_absent;
ο»Ώ
Pgsql
|
 emp_name |  emp_dept  | absent 
----------+------------+--------
 Alice    | Finance    | absent
 Bob      | Operations | absent
 Carol    | Finance    | absent
 David    | HR         | 
 Emily    | HR         | 
ο»Ώ

The query below uses the COALESCE() function on the absent column. It retrieves names and absences (with out of office for NULL values) for each employee.

Pgsql
|
SELECT emp_name, COALESCE(absent, 'out of office') AS DisplayAbsent FROM employee_absent;
ο»Ώ
Pgsql
|
 emp_name | displayabsent 
----------+---------------
 Alice    | absent
 Bob      | absent
 Carol    | absent
 David    | out of office
 Emily    | out of office
ο»Ώ

Case #7: Error Output in COALESCE()

When specifying arguments with different datatypes, they should be convertible.

Pgsql
|
Select Coalesce ('x',NULL,1);
ο»Ώ

If the datatypes cannot be converted, the COALESCE() function will generate an error, as shown below.

Pgsql
|
ERROR:  invalid input syntax for type integer: "x"
ο»Ώ

ο»Ώ

Updated 13 Sep 2023
Did this page help you?
PREVIOUS
CURRENT_DATABASE
NEXT
NULLIF
Docs powered by
Archbee
TABLE OF CONTENTS
Overview
Syntax
Examples
Case #1: Returning the First Non-Null Value
Case #2: Handling NULL Value as the Last Argument
Case #3: Handling NULL Value as the First Argument
Case #4: Handling Multiple NULL Values
Case #5: Handling All NULL Values
Case #6: COALESCE() with Table Data
Case #7: Error Output in COALESCE()
Docs powered by
Archbee

Dedicated to data analytics that provides 10x faster execution of analytical queries than current state of the art solutions. We are launching SOON! Join the waiting list for more info.




Β©2023 Oxla