---
title: Conditional Functions for Unknowns
description: Conditional functions evaluate expressions to determine if the
  values and formulas meet the specified condition.
pubDate: 2026-08-17T09:53:44.266Z
antora:
  editUrl: https://github.com/couchbaselabs/docs-devex/edit/capella/modules/n1ql/pages/n1ql-language-reference/condfununknown.adoc
  xref: xref:cloud:n1ql:n1ql-language-reference/condfununknown.adoc[]
---

[Consult the llms.txt file for a full list of contents](/llms.txt)
[View original HTML](/cloud/n1ql/n1ql-language-reference/condfununknown.html)

# Conditional Functions for Unknowns

Conditional functions evaluate expressions to determine if the values and formulas meet the specified condition.

## [](#coalesce)COALESCE(`expression1`, `expression2`, ...)

Alias for [IFMISSINGORNULL()](#if%5Fmissing%5For%5Fnull).

## [](#decode)DECODE(`expression`, `search1`, `result1` \[, `search2`, `result2`, ...\] \[, `default`\])

### [](#arguments)Arguments

expression

\[Required\] Any valid expression.

search1, search2, ...

\[At least 1 is required\] Any values.

result1, result2, ...

\[At least 1 is required\] Any values.

default

\[Optional\] Any value.

The function requires a minimum of three arguments. The first argument is the _expression_. This is followed by one or more pairs of _search_ and _result_ arguments. If there is an even number of arguments, the last argument is the _default_ argument. If there is an odd number of arguments, the _default_ is not specified.

### [](#return-value)Return Value

Returns the _result_ corresponding to the first _search_ that matches the _expression_. If none of the _search_ values match the _expression_, the function returns the value of _default_, or returns NULL if _default_ is not specified.

### [](#example)Example

To try the examples in this section, set the query context to the `inventory` scope in the travel sample dataset. For more information, see [Query Context](../n1ql-intro/queriesandresults.md#query-context).

Find the UTC offset of all airports in the United States whose altitude is greater than 1000:

```sqlpp
SELECT a.airportname AS Airport,
DECODE(a.tz, "Pacific/Honolulu", "-10:00",
             "America/Anchorage", "-09:00",
             "America/Los_Angeles", "-08:00",
             "America/Denver", "-07:00",
             "America/Chicago", "-06:00",
             "America/New_York", "-05:00", a.tz) AS UTCOffset
FROM airport a
WHERE a.country = "United States" AND a.geo.alt > 1000
LIMIT 5;
```

Results

```json
[
  {
    "Airport": "Indian Mountain Lrrs",
    "UTCOffset": "-09:00"
  },
  {
    "Airport": "Sparrevohn Lrrs",
    "UTCOffset": "-09:00"
  },
  {
    "Airport": "Bicycle Lake Aaf",
    "UTCOffset": "-08:00"
  },
  {
    "Airport": "Twentynine Palms Eaf",
    "UTCOffset": "-0:800"
  },
  {
    "Airport": "Grants Milan Muni",
    "UTCOffset": "-07:00"
  }
]
```

## [](#if%5Fmissing)IFMISSING(`expression1`, `expression2`, ...)

### [](#arguments-2)Arguments

expression1, expression2, ...

\[At least 2 are required\] Any valid expressions.

### [](#return-value-2)Return Value

Returns the first non-MISSING value. Returns NULL if all values are MISSING.

### [](#example-2)Example

```sqlpp
SELECT IFMISSING(null, missing, "abc", 123) AS Mix, (1)
       IFMISSING(null, null, null) AS AllNull, (2)
       IFMISSING(missing, missing, missing) AS AllMissing;
```

Results

```json
[
  {
    "AllMissing": null,
    "AllNull": null, (2)
    "Mix": null (1)
  }
]
```

| **1** | The first non-MISSING value is NULL, so this function returns NULL. |
| ----- | ------------------------------------------------------------------- |
| **2** | The first non-MISSING value is NULL, so this function returns NULL. |

## [](#if%5Fmissing%5For%5Fnull)IFMISSINGORNULL(`expression1`, `expression2`, ...)

This function has an alias [COALESCE()](#coalesce).

### [](#arguments-3)Arguments

expression1, expression2, ...

\[At least 2 are required\] Any valid expressions.

### [](#return-value-3)Return Value

Returns first non-NULL, non-MISSING value. Returns NULL if all values are MISSING or NULL.

### [](#example-3)Example

```sqlpp
SELECT IFMISSINGORNULL(null, missing, "abc", 123) AS Mix,
       IFMISSINGORNULL(null, null, null) AS AllNull,
       IFMISSINGORNULL(missing, missing, missing) AS AllMissing;
```

Results

```json
[
  {
    "AllMissing": null,
    "AllNull": null,
    "Mix": "abc"
  }
]
```

## [](#if%5Fnull)IFNULL(`expression1`, `expression2`, ...)

### [](#arguments-4)Arguments

expression1, expression2, ...

\[At least 2 are required\] Any valid expressions.

### [](#return-value-4)Return Value

Returns first non-NULL value. Returns NULL if all values are NULL.

### [](#example-4)Example

```sqlpp
SELECT IFNULL(null, missing, "abc", 123) AS Mix, (1)
       IFNULL(null, null, null) AS AllNull,
       IFNULL(missing, missing, missing) AS AllMissing; (2)
```

Results

```json
[
  {
    "AllNull": null
  }
]
```

| **1** | The first non-NULL value is MISSING, so this function returns MISSING. |
| ----- | ---------------------------------------------------------------------- |
| **2** | The first non-NULL value is MISSING, so this function returns MISSING. |

## [](#missing%5Fif)MISSINGIF(`expression1`, `expression2`)

### [](#arguments-5)Arguments

expression1, expression2, ...

\[Exactly 2 are required\] Any valid expressions.

### [](#return-value-5)Return Value

Returns MISSING if `expression1` \= `expression2`, otherwise returns `expression1`. Returns MISSING if either input is MISSING or if both inputs are MISSING. Returns NULL if either input is NULL or if both inputs are NULL.

### [](#example-5)Example

```sqlpp
SELECT MISSINGIF("abc", 123) AS Different,
       MISSINGIF("abc", "abc") AS Same;
```

Results

```json
[
  {
    "Different": "abc"
  }
]
```

## [](#null%5Fif)NULLIF(`expression1`, `expression2`)

### [](#arguments-6)Arguments

expression1, expression2, ...

\[Exactly 2 are required\] Any valid expressions.

### [](#return-value-6)Return Value

Returns NULL if `expression1` \= `expression2`, otherwise returns `expression1`. Returns MISSING if either input is MISSING or if both inputs are MISSING. Returns NULL if either input is NULL or if both inputs are NULL.

### [](#example-6)Example

```sqlpp
SELECT NULLIF("abc", 123) AS Different,
       NULLIF("abc", "abc") AS Same;
```

Results

```json
[
  {
    "Different": "abc",
    "Same": null
  }
]
```

## [](#nvl)NVL(`expression1`, `expression2`)

### [](#arguments-7)Arguments

expression1, expression2, ...

\[Exactly 2 are required\] Any valid expressions.

### [](#return-value-7)Return Value

Returns `expression1` if `expression1` is not MISSING or NULL. Returns `expression2` if `expression1` is MISSING or NULL.

### [](#example-7)Example

To try the examples in this section, set the query context to the `inventory` scope in the travel sample dataset. For more information, see [Query Context](../n1ql-intro/queriesandresults.md#query-context).

```sqlpp
SELECT name as Name, NVL(iata, "n/a") as IATA
FROM airline
LIMIT 5;
```

Results

```json
[
  {
    "IATA": "Q5",
    "Name": "40-Mile Air"
  },
  {
    "IATA": "TQ",
    "Name": "Texas Wings"
  },
  {
    "IATA": "A1",
    "Name": "Atifly"
  },
  {
    "IATA": "n/a",
    "Name": "Jc royal.britannica"
  },
  {
    "IATA": "ZQ",
    "Name": "Locair"
  }
]
```

## [](#nvl2)NVL2(`expression`, `value1`, `value2`)

### [](#arguments-8)Arguments

expression

\[Required\] Any valid expression.

value1, value2, ...

\[Exactly 2 are required\] Any values.

### [](#return-value-8)Return Value

Returns `value1` if `expression` is not MISSING or NULL. Returns `value2` if `expression` is MISSING or NULL.

### [](#example-8)Example

To try the examples in this section, set the query context to the `inventory` scope in the travel sample dataset. For more information, see [Query Context](../n1ql-intro/queriesandresults.md#query-context).

```sqlpp
SELECT name as Name, NVL2(directions, "Yes", "No") as DirectionsAvailable
FROM hotel
LIMIT 5;
```

Results

```json
[
  {
    "DirectionsAvailable": "No",
    "Name": "Medway Youth Hostel"
  },
  {
    "DirectionsAvailable": "No",
    "Name": "The Balmoral Guesthouse"
  },
  {
    "DirectionsAvailable": "Yes",
    "Name": "The Robins"
  },
  {
    "DirectionsAvailable": "Yes",
    "Name": "Le Clos Fleuri"
  },
  {
    "DirectionsAvailable": "Yes",
    "Name": "Glasgow Grand Central"
  }
]
```