> ## Documentation Index
> Fetch the complete documentation index at: https://docs.oxla.com/llms.txt
> Use this file to discover all available pages before exploring further.

# coalesce()

## Overview

Tables can hold null and non-null values. Yet, often we prefer to overlook those null values and this is where `COALESCE()` steps in. It helps when we want to ignore null values while processing the data, by returning the first argument that is not null, while the remaining arguments from the first non-null argument are not evaluated.

<Info>If all arguments are null, the COALESCE function will return null.</Info>

## Syntax

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

```sql theme={null}
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.

```sql theme={null}
SELECT COALESCE(9, 3, 8, 7, 1);
```

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

```sql theme={null}
 coalesce 
----------
        9
```

### Case #2: Handling NULL Value as the Last Argument

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

```sql theme={null}
Select COALESCE(3,4,5,9,10,NULL);
```

&#x20;The function output is `3` because it returns the first non-null value.

```sql theme={null}
 coalesce 
----------
        3
```

### Case #3: Handling NULL Value as the First Argument

Consider NULL as the first argument in the following example.

```sql theme={null}
Select COALESCE(NULL,1,5,7,9,2);
```

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

```sql theme={null}
 coalesce 
----------
        1
```

### Case #4: Handling Multiple NULL Values

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

```sql theme={null}
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.

```sql theme={null}
 coalesce 
----------
        3
```

### Case #5: Handling All NULL Values

Assume that the given values are entirely composed of nulls.

```sql theme={null}
Select COALESCE(NULL, NULL ,NULL, NULL);
```

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

```sql theme={null}
 coalesce 
----------
 
```

### Case #6: `COALESCE()` with Table Data

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

```sql theme={null}
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:

```sql theme={null}
SELECT * FROM employee_absent;
```

```sql theme={null}
 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.

```sql theme={null}
SELECT emp_name, COALESCE(absent, 'out of office') AS DisplayAbsent FROM employee_absent;
```

```sql theme={null}
 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.&#x20;

```sql theme={null}
Select Coalesce ('x',NULL,1);
```

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

```sql theme={null}
ERROR:  invalid input syntax for type integer: "x"
```