---
title: SQL conditional functions
description: Use conditional functions to conditionally handle null values in SQL queries.
url: https://docs.influxdata.com/influxdb3/cloud-dedicated/reference/sql/functions/conditional/
estimated_tokens: 5715
product: InfluxDB Cloud Dedicated
version: cloud-dedicated
---

# SQL conditional functions

The InfluxDB Cloud Dedicated SQL implementation supports the following conditional functions for conditionally handling *null* values:

-   [coalesce](#coalesce)
-   [greatest](#greatest)
-   [ifnull](#ifnull)
-   [least](#least)
-   [nullif](#nullif)
-   [nvl](#nvl)
-   [nvl2](#nvl2)

## coalesce

Returns the first of its arguments that is not *null*. Returns *null* if all arguments are *null*. This function is often used to substitute a default value for *null* values.

```sql
coalesce(expression1[, ..., expression_n])
```

##### Arguments

-   **expression1, expression\_n**: Expression to use if previous expressions are *null*. Can be a constant, column, or function, and any combination of arithmetic operators. Pass as many expression arguments as necessary.

[](#view-coalesce-query-example)

View `coalesce` query example

```sql
SELECT
  val1,
  val2,
  val3,
  coalesce(val1, val2, val3, 'quz') AS coalesce
FROM
  (values ('foo', 'bar', 'baz'),
          (NULL, 'bar', 'baz'),
          (NULL, NULL, 'baz'),
          (NULL, NULL, NULL)
  ) data(val1, val2, val3)
```

| val1 | val2 | val3 | coalesce |
| --- | --- | --- | --- |
| foo | bar | baz | foo |
|  | bar | baz | bar |
|  |  | baz | baz |
|  |  |  | quz |

## greatest

Returns the greatest value in a list of expressions. Returns *null* if all expressions are *null*.

```sql
greatest(expression1[, ..., expression_n])
```

##### Arguments

-   **expression1, expression\_n**: Expressions to compare and return the greatest value. Can be a constant, column, or function, and any combination of arithmetic operators. Pass as many expression arguments as necessary.

[](#view-greatest-query-example)

View `greatest` query example

*The following example uses the [Random numbers sample dataset](/influxdb3/cloud-dedicated/reference/sample-data/#random-numbers-sample-data).*

```sql
SELECT time, greatest(a, b) AS greatest FROM numbers LIMIT 4
```

| time | greatest |
| --- | --- |
| 2023-01-01T01:20:00 | -0.515490223280789 |
| 2023-01-01T01:21:00 | 0.803201312042983 |
| 2023-01-01T01:22:00 | 0.970938142399892 |
| 2023-01-01T01:23:00 | 0.0493748366311344 |

## ifnull

*Alias of [nvl](#nvl).*

## least

Returns the least value in a list of expressions. Returns *null* if all expressions are *null*.

```sql
least(expression1[, ..., expression_n])
```

##### Arguments

-   **expression1, expression\_n**: Expressions to compare and return the least value. Can be a constant, column, or function, and any combination of arithmetic operators. Pass as many expression arguments as necessary.

[](#view-least-query-example)

View `least` query example

*The following example uses the [Random numbers sample dataset](/influxdb3/cloud-dedicated/reference/sample-data/#random-numbers-sample-data).*

```sql
SELECT time, least(a, b) AS least FROM numbers LIMIT 4
```

| time | least |
| --- | --- |
| 2023-01-01T00:10:00 | -1.08759833527982 |
| 2023-01-01T00:11:00 | -0.187620086586211 |
| 2023-01-01T00:12:00 | -0.81371037157091 |
| 2023-01-01T00:13:00 | -0.339781659874945 |

## nullif

Returns *null* if *expression1* equals *expression2*; otherwise it returns *expression1*. This can be used to perform the inverse operation of [`coalesce`](#coalesce).

```sql
nullif(expression1, expression2)
```

##### Arguments

-   **expression1**: Expression to compare and return if equal to expression2. Can be a constant, column, or function, and any combination of arithmetic operators.
-   **expression2**: Expression to compare to expression1. Can be a constant, column, or function, and any combination of arithmetic operators.

[](#view-nullif-query-example)

View `nullif` query example

```sql
SELECT
  value,
  nullif(value, 'baz') AS nullif
FROM
  (values ('foo'),
          ('bar'),
          ('baz')
  ) data(value)
```

| value | nullif |
| --- | --- |
| foo | foo |
| bar | bar |
| baz |  |

## nvl

Returns *expression2* if *expression1* is *null*; otherwise it returns *expression1*.

```sql
nvl(expression1, expression2)
```

##### Arguments

-   **expression1**: Return this expression if not *null*. Can be a constant, column, or function, and any combination of arithmetic operators.
-   **expression2**: Return this expression if *expression1* is *null*. Can be a constant, column, or function, and any combination of arithmetic operators.

[](#view-nvl-query-example)

View `nvl` query example

```sql
SELECT
  value,
  nvl(value, 'baz') AS nvl
FROM
  (values ('foo'),
          ('bar'),
          (NULL)
  ) data(value)
```

| value | nvl |
| --- | --- |
| foo | foo |
| bar | bar |
|  | baz |

## nvl2

Returns *expression2* if *expression1* is **not** *null*; otherwise it returns *expression3*.

```sql
nvl2(expression1, expression2, expression3)
```

##### Arguments

-   **expression1**: First expression to test for *null*. Can be a constant, column, or function, and any combination of operators.
-   **expression2**: Second expression to return if *expression1* is not *null*. Can be a constant, column, or function, and any combination of operators.
-   **expression3**: Expression to return if *expression1* is *null*. Can be a constant, column, or function, and any combination of operators.

[](#view-nvl2-query-example)

View `nvl2` query example

```sql
SELECT
  val1,
  val2,
  val3,
  nvl2(val1, val2, val3) AS nvl2
FROM
  (values ('foo', 'bar', 'baz'),
          (NULL, 'bar', 'baz'),
          (NULL, NULL, 'baz'),
  ) data(val1, val2, val3)
```

| val1 | val2 | val3 | nvl2 |
| --- | --- | --- | --- |
| foo | bar | baz | bar |
|  | bar | baz | baz |
|  |  | baz | baz |