# Conditional Functions for Unknowns

• reference
+

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

## COALESCE(`expression1`, `expression2`, ...)

Alias for IFMISSINGORNULL().

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

### 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

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

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.

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

``````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
``````[
{
"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"
}
]``````

## IFMISSING(`expression1`, `expression2`, ...)

### Arguments

expression1, expression2, ...

[At least 2 are required] Any valid expressions.

### Return Value

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

### Example

``````SELECT IFMISSING(null, missing, "abc", 123) AS Mix, (1)
IFMISSING(null, null, null) AS AllNull, (2)
IFMISSING(missing, missing, missing) AS AllMissing;``````
Results
``````[
{
"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.

## IFMISSINGORNULL(`expression1`, `expression2`, ...)

This function has an alias COALESCE().

### Arguments

expression1, expression2, ...

[At least 2 are required] Any valid expressions.

### Return Value

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

### Example

``````SELECT IFMISSINGORNULL(null, missing, "abc", 123) AS Mix,
IFMISSINGORNULL(null, null, null) AS AllNull,
IFMISSINGORNULL(missing, missing, missing) AS AllMissing;``````
Results
``````[
{
"AllMissing": null,
"AllNull": null,
"Mix": "abc"
}
]``````

## IFNULL(`expression1`, `expression2`, ...)

### Arguments

expression1, expression2, ...

[At least 2 are required] Any valid expressions.

### Return Value

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

### Example

``````SELECT IFNULL(null, missing, "abc", 123) AS Mix, (1)
IFNULL(null, null, null) AS AllNull,
IFNULL(missing, missing, missing) AS AllMissing; (2)``````
Results
``````[
{
"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.

## MISSINGIF(`expression1`, `expression2`)

### Arguments

expression1, expression2, ...

[Exactly 2 are required] Any valid expressions.

### 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

``````SELECT MISSINGIF("abc", 123) AS Different,
MISSINGIF("abc", "abc") AS Same;``````
Results
``````[
{
"Different": "abc"
}
]``````

## NULLIF(`expression1`, `expression2`)

### Arguments

expression1, expression2, ...

[Exactly 2 are required] Any valid expressions.

### 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

``````SELECT NULLIF("abc", 123) AS Different,
NULLIF("abc", "abc") AS Same;``````
Results
``````[
{
"Different": "abc",
"Same": null
}
]``````

## NVL(`expression1`, `expression2`)

### Arguments

expression1, expression2, ...

[Exactly 2 are required] Any valid expressions.

### Return Value

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

### 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.

``````SELECT name as Name, NVL(iata, "n/a") as IATA
FROM airline
LIMIT 5;``````
Results
``````[
{
"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(`expression`, `value1`, `value2`)

### Arguments

expression

[Required] Any valid expression.

value1, value2, ...

[Exactly 2 are required] Any values.

### Return Value

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

### 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.

``````SELECT name as Name, NVL2(directions, "Yes", "No") as DirectionsAvailable
FROM hotel
LIMIT 5;``````
Results
``````[
{
"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"
}
]``````
Output