# Conditional Expressions

#### <mark style="color:purple;">CASE</mark>

Returns the result of the first condition evaluating to true or returns the default value if all conditions evaluate to false.&#x20;

```sql
SYNTAX - CASE
             WHEN condition1 THEN result1
             [WHEN condition2 THEN result2 ]
             [WHEN ......]
             [ELSE default ]
         END
```

```sql
> select col1,
               case
                    when col1 < 0 then 'res1'
                    when col1 > 0 then 'res2'
                    else 'res3'
               end as result
           from table
```

#### <mark style="color:purple;">COALESCE</mark>

Returns the first non-null value from a list of expressions.

```sql
coalesce(val1, val2, val3 ......)
```

```sql
> select coalesce(NULL,20,NULL,NULL,'String1');
20
```

#### <mark style="color:purple;">NULLIF(</mark>  value1,  value2  <mark style="color:purple;">)</mark>

Returns null if `value1` equals `value2`, otherwise returns `value1`

```sql
> select nullif(24, 24);
null

> select nullif(12, null);
12
```

#### <mark style="color:purple;">GREATEST(</mark>  \<expr1>,  \<expr2>,  \<expr3>......\<exprN>  <mark style="color:purple;">)</mark>

Returns the largest value among the given values.

```sql
select greatest(100, 12, 23, 1999, 2)
1999
```

#### <mark style="color:purple;">LEAST(</mark>  \<expr1>,  \<expr2>,  \<expr3>......\<exprN>  )

Returns the smallest value among the given values.

```sql
select least(-12, 0, 10000, -527)
-527 
```

#### <mark style="color:purple;">IS \[NOT] NULL(</mark>  \<expr>  )

Returns boolean based on the expr is null or is not null

```sql
select * from tab where col1 is null;

select * from tab where col1 is not null;
```

#### <mark style="color:purple;">NVL(</mark>  \<expr1>, \<expr2>  )

If *expr1* is NULL, returns *expr2*, otherwise returns *expr1*.

```sql
> select nvl(null, 'e6data');
e6data
```

#### <mark style="color:purple;">NVL2 (</mark>  \<expr1>, \<expr2>, \<expr3>  )

Returns values depending on whether the first input is NULL. If *expr1* is NOT NULL, then NVL2 returns expr&#x32;*`.`*&#x49;f *expr1* is NULL, then NVL2 returns expr3.

```sql
> select nvl2('e6', 'e6data', 'data');
e6data

> select nvl2('e6', 'e6data', 'data');
data
```

#### <mark style="color:purple;">DECODE(</mark>  \<expr> , \<search\_1> , \<result\_1> \[ , \<search\_2> , \<result\_2> ... ] \[ , \<default>] )

Compares each search expression sequentially against the select expression. Upon finding a match, it returns the corresponding result expression.

<pre class="language-sql"><code class="lang-sql"><strong>> with cte as(
</strong>    select 1 as c1
    union all
    select 2
    union all
    select (NULL)
    union all
    select 4
)
select c1,decode(c1,1,'one',2,'two',null,'-xyx','others')
from cte
order by 1,2
--
|1    |one
|2    |two
|4    |others
|null    | -xyx
</code></pre>