# coalesce {#coalesce}

**Parent topic:**[SQL Reference](../../Programming/SQLStatements/SQLStatement.md)

## Syntax {#syntax}

`coalesce(X1, X2, args...)`

## Arguments {#arguments}

**X1** is a scalar or vector.

**X2** is a scalar or vector of the same data type as *X1*. If *X1* is a scalar, *X2* must be a scalar; If *X1* is a vector, *X2* can be a non-null scalar or a vector of the same length as *X1*.

**args** \(optional\) can be one or more arguments taking the same data type/form as *X2*.

## Details {#details}

The function fills null values in *X1* and returns a scalar or vector of the same dimension as *X1*.

For each element in *X1*,

-   If not null, return the element;

-   If null, check the element at the same position in *X2*:

    -   If not null, fill the null value in *X1* with it;

    -   If null, conduct the aforementioned calculation on the subsequent args until a non-null element is returned; Otherwise return NULL.


Usage:

-   Merge multiple columns of a table into one column;

-   An alternative to complex `case` expression. For example, `select coalesce (expr1, expr2, 1) from t` is equivalent to `select case when vol1 is not null then vol1 when vol2 is not null then vol2 else 1 end from t`.


## Examples {#examples}

```python
coalesce(int(NULL), int(NULL), 1, 3)
//output: 1

coalesce(-1 NULL 4 3, NULL 2 NULL 1, 1 4 5 2)
//output: [-1,2,4,3]

vol1 = [3.3, 2.2, 2.1, NULL, 1.2]
vol2 = [NULL, 1.8, 1.9, 2.3, 3.2]
sym = `a`a`b`a`c
t = table(sym, vol1, vol2)

select sym, coalesce(vol1, vol2) as vol from t
```

|sym|vol|
|---|---|
|a|3.3|
|a|2.2|
|b|2.1|
|a|2.3|
|c|1.2|

