# Conditional expressions
<a name="sql-functions-conditional-expressions-spark"></a>

In SQL, conditional expressions are used to make decisions based on certain conditions. They allow you to control the flow of your SQL statements and return different values or perform different actions based on the evaluation of one or more conditions.

AWS Clean Rooms supports the following conditional expressions:

**Topics**
+ [CASE conditional expression](CASE_function.md)
+ [COALESCE expression](coalesce-function.md)
+ [GREATEST and LEAST expression](GREATEST_LEAST.md)
+ [IF expression](IF.md)
+ [IS\$1NULL expression](IS_NULL.md)
+ [IS\$1NOT\$1NULL expression](IS_NOT_NULL.md)
+ [NVL and COALESCE functions](NVL_function.md)
+ [NVL2 function](NVL2.md)
+ [NULLIF function](NULLIF_function.md)

# CASE conditional expression
<a name="CASE_function"></a>

The CASE expression is a conditional expression, similar to if/then/else statements found in other languages. CASE is used to specify a result when there are multiple conditions. Use CASE where a SQL expression is valid, such as in a SELECT command.

There are two types of CASE expressions: simple and searched.
+ In simple CASE expressions, an expression is compared with a value. When a match is found, the specified action in the THEN clause is applied. If no match is found, the action in the ELSE clause is applied.
+ In searched CASE expressions, each CASE is evaluated based on a Boolean expression, and the CASE statement returns the first matching CASE. If no match is found among the WHEN clauses, the action in the ELSE clause is returned.

## Syntax
<a name="CASE_function-syntax"></a>

Simple CASE statement used to match conditions:

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

Searched CASE statement used to evaluate each condition:

```
CASE
  WHEN condition THEN result
  [WHEN ...]
  [ELSE result]
END
```

## Arguments
<a name="CASE_function-arguments"></a>

 *expression*   
A column name or any valid expression.

 *value*   
Value that the expression is compared with, such as a numeric constant or a character string.

 *result*   
The target value or expression that is returned when an expression or Boolean condition is evaluated. The data types of all the result expressions must be convertible to a single output type.

 *condition*   
A Boolean expression that evaluates to true or false. If *condition* is true, the value of the CASE expression is the result that follows the condition, and the remainder of the CASE expression is not processed. If *condition* is false, any subsequent WHEN clauses are evaluated. If no WHEN condition results are 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.

## Examples
<a name="CASE_function-examples"></a>

Use a simple CASE expression to replace `New York City` with `Big Apple` in a query against the VENUE table. Replace all other city names with `other`.

```
select venuecity,
  case venuecity
    when 'New York City'
    then 'Big Apple' else 'other'
  end 
from venue
order by venueid desc;

venuecity        |   case
-----------------+-----------
Los Angeles      | other
New York City    | Big Apple
San Francisco    | other
Baltimore        | other
...
```

Use a searched CASE expression to assign group numbers based on the PRICEPAID value for individual ticket sales:

```
select pricepaid,
  case when pricepaid <10000 then 'group 1'
    when pricepaid >10000 then 'group 2'
    else 'group 3'
  end 
from sales
order by 1 desc;

pricepaid |  case
----------+---------
12624     | group 2
10000     | group 3
10000     | group 3
9996      | group 1
9988      | group 1
...
```

# COALESCE expression
<a name="coalesce-function"></a>

A COALESCE expression returns the value of the first expression in the list that is not null. If all expressions are null, the result is null. When a non-null value is found, the remaining expressions in the list are not evaluated.

This type of expression is useful when you want to return a backup value for something when the preferred value is missing or null. For example, a query might return one of three phone numbers (cell, home, or work, in that order), whichever is found first in the table (not null).

## Syntax
<a name="coalesce-function-syntax"></a>

```
COALESCE (expression, expression, ... )
```

## Examples
<a name="coalesce-function-examples"></a>

Apply COALESCE expression to two columns.

```
select coalesce(start_date, end_date)
from datetable
order by 1;
```

The default column name for an NVL expression is COALESCE. The following query returns the same results.

```
select coalesce(start_date, end_date) from datetable order by 1;
```

# GREATEST and LEAST expression
<a name="GREATEST_LEAST"></a>

Returns the largest or smallest value from a list of any number of expressions.

## Syntax
<a name="GREATEST_LEAST-synopsis"></a>

```
GREATEST (value [, ...])
LEAST (value [, ...])
```

## Parameters
<a name="GREATEST_LEAST-arguments"></a>

*expression\$1list*  
A comma-separated list of expressions, such as column names. The expressions must all be convertible to a common data type. NULL values in the list are ignored. If all of the expressions evaluate to NULL, the result is NULL.

## Returns
<a name="GREATEST_LEAST-returns"></a>

Returns the greatest (for GREATEST) or least (for LEAST) value from the provided list of expressions.

## Example
<a name="GREATEST_LEAST-examples"></a>

The following example returns the highest value alphabetically for `firstname` or `lastname`.

```
select firstname, lastname, greatest(firstname,lastname) from users
where userid < 10
order by 3;

 firstname | lastname  | greatest
-----------+-----------+-----------
 Alejandro | Rosalez   | Ratliff
 Carlos    | Salazar   | Carlos
 Jane      | Doe       | Doe
 John      | Doe       | Doe
 John      | Stiles    | John
 Shirley   | Rodriguez | Rodriguez
 Terry     | Whitlock  | Terry
 Richard   | Roe       | Richard
 Xiulan    | Wang      | Wang
(9 rows)
```

# IF expression
<a name="IF"></a>

The IF conditional function returns one of two values based on a condition. 

This function is a common control flow statement used in SQL to make decisions and return different values based on the evaluation of a condition. It's useful for implementing simple if-else logic within a query.

## Syntax
<a name="IF-syntax"></a>

```
if(expr1, expr2, expr3) 
```

## Arguments
<a name="IF-arguments"></a>

*expr1*  
The condition or expression that is evaluated. If it is `true`, the function will return the value of *expr2*. If *expr1* is `false`, the function will return the value of *expr3*.

*expr2*  
The expression that is evaluated and returned if *expr1* is `true`.

*expr3*  
The expression that is evaluated and returned if *expr1* is `false`.

## Returns
<a name="IF-returns"></a>

If `expr1` evaluates to `true`, then returns `expr2`; otherwise returns `expr3`.

## Example
<a name="IF-example"></a>

The following example uses the `if()` function to return one of two values based on a condition. The condition being evaluated is `1 < 2`, which is `true`, so the first value `'a'` is returned.

```
SELECT if(1 < 2, 'a', 'b');
 a]
```

# IS\$1NULL expression
<a name="IS_NULL"></a>

The IS\$1NULL conditional expression is used to check if a value is null.

This expression is a synonym for IS NULL.

## Syntax
<a name="IS_NULL-syntax"></a>

```
is_null(expr)
```

## Arguments
<a name="IS_NULL-arguments"></a>

*expr*  
An expression of any type.

## Returns
<a name="IS_NULL-returns"></a>

The IS\$1NULL conditional expression returns a Boolean. If `expr1` is NULL, returns `true`, otherwise returns `false`.

## Examples
<a name="IS_NULL-example"></a>

The following example checks if the value `1` is null, and returns the boolean result `true` because 1 is a valid, non-null value.

```
SELECT is not null(1);
 true
```

The following example selects the `id` column from the `squirrels` table, but only for the rows where the age column is `null`.

```
SELECT id FROM squirrels WHERE is_null(age)
```

# IS\$1NOT\$1NULL expression
<a name="IS_NOT_NULL"></a>

The IS\$1NOT\$1NULL conditional expression is used to check if a value is not null.

This expression is a synonym for IS NOT NULL.

## Syntax
<a name="IS_NOT_NULL-syntax"></a>

```
is_not_null(expr)
```

## Arguments
<a name="IS_NOT_NULL-arguments"></a>

*expr*  
An expression of any type.

## Returns
<a name="IS_NOT_NULL-returns"></a>

The IS\$1NOT\$1NULL conditional expression returns a Boolean. If `expr1` is not NULL, returns `true`, otherwise returns `false`.

## Examples
<a name="IS_NOT_NULL-example"></a>

The following example checks if the value `1` is not null, and returns the boolean result `true` because 1 is a valid, non-null value.

```
SELECT is not null(1);
 true
```

The following example selects the `id` column from the `squirrels` table, but only for the rows where the age column is not `null`.

```
SELECT id FROM squirrels WHERE is_not_null(age)
```

# NVL and COALESCE functions
<a name="NVL_function"></a>

Returns the value of the first expression that isn't null in a series of expressions. When a non-null value is found, the remaining expressions in the list aren't evaluated. 

NVL is identical to COALESCE. They are synonyms. This topic explains the syntax and contains examples for both.

## Syntax
<a name="NVL_function-synopsis"></a>

```
NVL( expression, expression, ... )
```

The syntax for COALESCE is the same:

```
COALESCE( expression, expression, ... )
```

If all expressions are null, the result is null.

These functions are useful when you want to return a secondary value when a primary value is missing or null. For example, a query might return the first of three available phone numbers: cell, home, or work. The order of the expressions in the function determines the order of evaluation.

## Arguments
<a name="NVL_function-arguments"></a>

 *expression*   
An expression, such as a column name, to be evaluated for null status.

## Return type
<a name="NVL_function-returntype"></a>

AWS Clean Rooms determines the data type of the returned value based on the input expressions. If the data types of the input expressions don't have a common type, then an error is returned.

## Examples
<a name="NVL_function-examples"></a>

If the list contains integer expressions, the function returns an integer. 

```
SELECT COALESCE(NULL, 12, NULL);

coalesce
--------------
12
```

This example, which is the same as the previous example, except that it uses NVL, returns the same result. 

```
SELECT NVL(NULL, 12, NULL);

coalesce
--------------
12
```

The following example returns a string type.

```
SELECT COALESCE(NULL, 'AWS Clean Rooms', NULL);

coalesce
--------------
AWS Clean Rooms
```

The following example results in an error because the data types vary in the expression list. In this case, there is both a string type and a number type in the list.

```
SELECT COALESCE(NULL, 'AWS Clean Rooms', 12);
ERROR: invalid input syntax for integer: "AWS Clean Rooms"
```

# NVL2 function
<a name="NVL2"></a>

Returns one of two values based on whether a specified expression evaluates to NULL or NOT NULL.

## Syntax
<a name="NVL2-synopsis"></a>

```
NVL2 ( expression, not_null_return_value, null_return_value )
```

## Arguments
<a name="NVL2-arguments"></a>

 *expression*   
An expression, such as a column name, to be evaluated for null status.

 *not\$1null\$1return\$1value*   
The value returned if *expression* evaluates to NOT NULL. The *not\$1null\$1return\$1value* value must either have the same data type as *expression* or be implicitly convertible to that data type.

 *null\$1return\$1value*   
The value returned if *expression* evaluates to NULL. The *null\$1return\$1value* value must either have the same data type as *expression* or be implicitly convertible to that data type.

## Return type
<a name="NVL2-return-type"></a>

The NVL2 return type is determined as follows:
+ If either *not\$1null\$1return\$1value* or *null\$1return\$1value* is null, the data type of the not-null expression is returned.

If both *not\$1null\$1return\$1value* and *null\$1return\$1value* are not null:
+ If *not\$1null\$1return\$1value* and *null\$1return\$1value* have the same data type, that data type is returned.
+ If *not\$1null\$1return\$1value* and *null\$1return\$1value* have different numeric data types, the smallest compatible numeric data type is returned.
+ If *not\$1null\$1return\$1value* and *null\$1return\$1value* have different datetime data types, a timestamp data type is returned.
+ If *not\$1null\$1return\$1value* and *null\$1return\$1value* have different character data types, the data type of *not\$1null\$1return\$1value* is returned.
+ If *not\$1null\$1return\$1value* and *null\$1return\$1value* have mixed numeric and non-numeric data types, the data type of *not\$1null\$1return\$1value* is returned.

**Important**  
In the last two cases where the data type of *not\$1null\$1return\$1value* is returned, *null\$1return\$1value* is implicitly cast to that data type. If the data types are incompatible, the function fails.

## Usage notes
<a name="nvl2-usage-notes"></a>

For NVL2, the return will have the value of either the *not\$1null\$1return\$1value* or *null\$1return\$1value* parameter, whichever is selected by the function, but will have the data type of *not\$1null\$1return\$1value*.

For example, assuming column1 is NULL, the following queries will return the same value. However, the DECODE return value data type will be INTEGER and the NVL2 return value data type will be VARCHAR.

```
select decode(column1, null, 1234, '2345');
select nvl2(column1, '2345', 1234);
```

## Example
<a name="NVL2-examples"></a>

The following example modifies some sample data, then evaluates two fields to provide appropriate contact information for users: 

```
update users set email = null where firstname = 'Aphrodite' and lastname = 'Acevedo';

select (firstname + ' ' + lastname) as name, 
nvl2(email, email, phone) AS contact_info
from users 
where state = 'WA'
and lastname  like 'A%'
order by lastname, firstname;

name			     contact_info	
--------------------+-------------------------------------------
Aphrodite Acevedo	(555) 555-0100
Caldwell Acevedo 	Nunc.sollicitudin@example.ca
Quinn Adams		   vel@example.com
Kamal Aguilar		 quis@example.com
Samson Alexander	 hendrerit.neque@example.com
Hall Alford		   ac.mattis@example.com
Lane Allen		    et.netus@example.com
Xander Allison	   ac.facilisis.facilisis@example.com
Amaya Alvarado	   dui.nec.tempus@example.com
Vera Alvarez		  at.arcu.Vestibulum@example.com
Yetta Anthony		 enim.sit@example.com
Violet Arnold		 ad.litora@example.comm
August Ashley		 consectetuer.euismod@example.com
Karyn Austin		  ipsum.primis.in@example.com
Lucas Ayers		   at@example.com
```

# NULLIF function
<a name="NULLIF_function"></a>

Compares two arguments and returns null if the arguments are equal. If they aren't equal, the first argument is returned.

## Syntax
<a name="NULLIF_function-synopsis"></a>

The NULLIF expression compares two arguments and returns null if the arguments are equal. If they aren't equal, the first argument is returned. This expression is the inverse of the NVL or COALESCE expression.

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

## Arguments
<a name="NULLIF_function-arguments"></a>

 *expression1, expression2*   
The target columns or expressions that are compared. The return type is the same as the type of the first expression. 

## Examples
<a name="NULLIF_function-examples"></a>

In the following example, the query returns the string `first` because the arguments are not equal.

```
SELECT NULLIF('first', 'second');

case
-------
first
```

In the following example, the query returns `NULL` because the string literal arguments are equal.

```
SELECT NULLIF('first', 'first');

case
-------
NULL
```

In the following example, the query returns `1` because the integer arguments are not equal.

```
SELECT NULLIF(1, 2);

case
-------
1
```

In the following example, the query returns `NULL` because the integer arguments are equal.

```
SELECT NULLIF(1, 1);

case
-------
NULL
```

In the following example, the query returns null when the LISTID and SALESID values match:

```
select nullif(listid,salesid), salesid
from sales where salesid<10 order by 1, 2 desc;

listid  | salesid
--------+---------
     4  |       2
     5  |       4
     5  |       3
     6  |       5
     10 |       9
     10 |       8
     10 |       7
     10 |       6
        |       1
(9 rows)
```