---
schema_version: "1"
id: 634f8f3cc14047d365f40144f512f788ce5392b499b9b32330e6f11dc08addd5
title: Conditional Expressions
collection: developer
version: current
locale: en-us
portal_url: https://docs.salesforce.com/developer/current/en-us/data-cloud/data-cloud-query-guide/references/data-cloud-sql-reference/scalar-func/conditional.md
canonical_url: https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/conditional.html
source_site: developer.salesforce.com
content_type: documentation
content_hash: 3b6a72c480866ffef86db533cb833845f8e4020c380317fbfbe3794d03ecf4dd
products:
  - Data 360 Query Guide
  - Data 360 Developer Documentation
---
# Conditional Expressions

**Applies to:** ✅ Data 360 SQL ✅ Tableau Hyper API

The conditional expressions select one of multiple result values.

## `CASE`

The SQL CASE expression is a generic conditional expression, similar to if/else statements in other programming languages. CASE clauses can be used wherever an expression is valid CASE expressions have two variants.

```sql
CASE
    WHEN <condition> THEN <result>
    [WHEN ...]
    [ELSE <result>]
END

CASE <expression>
    WHEN <value> THEN <result>
    [WHEN ...]
    [ELSE <result>]
END
```

In the first variant, each `condition` is an expression that returns a boolean result. If the condition’s result is true, the value of the CASE expression is the `result` that follows the condition, and the rest of the CASE expression isn’t processed. If the condition’s result isn’t true, any subsequent WHEN clauses are examined in the same manner. If no WHEN `condition` yields true, the value of the CASE expression is the `result` of the ELSE clause. If the ELSE clause is omitted and no condition is true, the result is null.

For example, the query:

The table `test` is defined as:

```sql
a
--
1
2
3
```

```sql
SELECT a,
    CASE
        WHEN a=1 THEN 'one'
        WHEN a=2 THEN 'two'
        ELSE 'other'
    END
FROM test;
```

Returns the result:

```sql
a | case
--+-------
1 | one
2 | two
3 | other
```

The second form of CASE expressions, called a simple case expressions, is similar to a `switch` statement in C.

First, `expression` is computed and then compared to each of the `value` expressions in the WHEN clauses until one is found that is equal to it. If no match is found, the `result` of the ELSE clause (or a null value) is returned.

For example, the previous example can be written as a simple CASE expression.

```sql
SELECT a,
    CASE a
        WHEN 1 THEN 'one'
        WHEN 2 THEN 'two'
        ELSE 'other'
    END
FROM test;
```

A CASE expression doesn’t evaluate any subexpressions that aren’t required to determine the result. For example, this CASE expression is a potential way to avoid a division-by-zero failure.

```sql
SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;
```

The data types of all the `result` expressions must be convertible to a single output type.

## `COALESCE`

```sql
coalesce(<value>, ...)
```

The `COALESCE` function returns the first of its arguments that isn't null. Null is returned only if all arguments are null. It’s often used to substitute a default value for null values if data is retrieved for display, for example:

```sql
SELECT coalesce(description, short_description, '(none)') ...
```

This function returns `description` if it isn’t null. Or it returns `short_description` if it isn’t null. Otherwise, it returns `(none)`.

`COALESCE` only evaluates the arguments that are needed to determine the result. Arguments after the first non-null argument aren’t evaluated.

## `NULLIF`

```sql
nullif(<value1>, <value2>)
```

The `NULLIF` function returns a null value if `value1` equals `value2`. Otherwise, it returns `value1`. This function can be used to perform the inverse operation of the previous `COALESCE` example.

```sql
SELECT nullif(value, '(none)')
```

In this example, if `value` is `(none)`, NULLIF returns null, otherwise the value of `value` is returned.

## `GREATEST` and `LEAST`

```sql
greatest(value, ...)
least(value, ...)
greatest(PRESERVE NULLS value, ...)
least(PRESERVE NULLS value, ...)
```

The `GREATEST` and `LEAST` functions choose the largest or smallest value from a list of expressions. The expressions must all be convertible to a common data type, and is the type of the result.

By default, NULL values in the list are ignored. The result is NULL if all of the inputs evaluate to NULL. To preserve NULL inputs, use \`PRESERVE NULLS. In this case, the result is NULL if any input is NULL.