Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/). 

# Conditional expressions
<a name="c_conditional_expressions"></a>

**Topics**
+ [

# CASE conditional expression
](r_CASE_function.md)
+ [

# DECODE function
](r_DECODE_expression.md)
+ [

# GREATEST and LEAST functions
](r_GREATEST_LEAST.md)
+ [

# NVL and COALESCE functions
](r_NVL_function.md)
+ [

# NVL2 function
](r_NVL2.md)
+ [

# NULLIF function
](r_NULLIF_function.md)

Amazon Redshift supports some conditional expressions that are extensions to the SQL standard.

# CASE conditional expression
<a name="r_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="r_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="r_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="r_CASE_function-examples"></a>

The following examples use the VENUE table and SALES table from the sample TICKIT data. For more information, see [Sample database](c_sampledb.md).

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

# DECODE function
<a name="r_DECODE_expression"></a>

A DECODE expression replaces a specific value with either another specific value or a default value, depending on the result of an equality condition. This operation is equivalent to the operation of a simple CASE expression or an IF-THEN-ELSE statement.

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

```
DECODE ( expression, search, result [, search, result ]... [ ,default ] )
```

This type of expression is useful for replacing abbreviations or codes that are stored in tables with meaningful business values that are needed for reports.

## Parameters
<a name="r_DECODE_expression-parameters"></a>

 *expression*   
The source of the value that you want to compare, such as a column in a table.

 *search*   
The target value that is compared against the source expression, such as a numeric value or a character string. The search expression must evaluate to a single fixed value. You cannot specify an expression that evaluates to a range of values, such as `age between 20 and 29`; you need to specify separate search/result pairs for each value that you want to replace.  
The data type of all instances of the search expression must be the same or compatible. The *expression* and *search* parameters must also be compatible.

 *result*   
The replacement value that query returns when the expression matches the search value. You must include at least one search/result pair in the DECODE expression.  
The data types of all instances of the result expression must be the same or compatible. The *result* and *default* parameters must also be compatible.

 *default*   
An optional default value that is used for cases when the search condition fails. If you do not specify a default value, the DECODE expression returns NULL.

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

If the *expression* value and the *search* value are both NULL, the DECODE result is the corresponding *result* value. For an illustration of this use of the function, see the Examples section.

When used this way, DECODE is similar to [NVL2 function](r_NVL2.md), but there are some differences. For a description of these differences, see the NVL2 usage notes.

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

When the value `2008-06-01` exists in the caldate column of datetable, the following example replaces it with `June 1st, 2008`. The example replaces all other caldate values with NULL. 

```
select decode(caldate, '2008-06-01', 'June 1st, 2008')
from datetable where month='JUN' order by caldate;

case
----------------
June 1st, 2008

...
(30 rows)
```

The following example uses a DECODE expression to convert the five abbreviated CATNAME columns in the CATEGORY table to full names and convert other values in the column to `Unknown`. 

```
select catid, decode(catname,
'NHL', 'National Hockey League',
'MLB', 'Major League Baseball',
'MLS', 'Major League Soccer',
'NFL', 'National Football League',
'NBA', 'National Basketball Association',
'Unknown')
from category
order by catid;

catid  |	case
-------+---------------------------------
1      | Major League Baseball
2      | National Hockey League
3      | National Football League
4      | National Basketball Association
5      | Major League Soccer
6      | Unknown
7      | Unknown
8      | Unknown
9      | Unknown
10     | Unknown
11     | Unknown
(11 rows)
```

Use a DECODE expression to find venues in Colorado and Nevada with NULL in the VENUESEATS column; convert the NULLs to zeroes. If the VENUESEATS column is not NULL, return 1 as the result. 

```
select venuename, venuestate, decode(venueseats,null,0,1)
from venue
where venuestate in('NV','CO')
order by 2,3,1;

venuename	              | venuestate     | case
------------------------------+----------------+-----------
Coors Field                   |	CO	       |   1
Dick's Sporting Goods Park    |	CO	       |   1
Ellie Caulkins Opera House    |	CO	       |   1
INVESCO Field		      |	CO	       |   1
Pepsi Center		      |	CO	       |   1
Ballys Hotel		      |	NV	       |   0
Bellagio Hotel                |	NV	       |   0
Caesars Palace                |	NV	       |   0
Harrahs Hotel                 |	NV	       |   0
Hilton Hotel                  |	NV	       |   0
...						
(20 rows)
```

# GREATEST and LEAST functions
<a name="r_GREATEST_LEAST"></a>

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

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

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

## Parameters
<a name="r_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="r_GREATEST_LEAST-returns"></a>

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

## Example
<a name="r_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
-----------+-----------+-----------
 Lars      | Ratliff   | Ratliff
 Reagan    | Hodge     | Reagan
 Colton    | Roy       | Roy
 Barry     | Roy       | Roy
 Tamekah   | Juarez    | Tamekah
 Rafael    | Taylor    | Taylor
 Victor    | Hernandez | Victor
 Vladimir  | Humphrey  | Vladimir
 Mufutau   | Watkins   | Watkins
(9 rows)
```

# NVL and COALESCE functions
<a name="r_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="r_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="r_NVL_function-arguments"></a>

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

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

Amazon Redshift 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="r_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, 'Amazon Redshift', NULL);

coalesce
--------------
Amazon Redshift
```

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, 'Amazon Redshift', 12);
ERROR: invalid input syntax for integer: "Amazon Redshift"
```

For this example, you create a table with START\$1DATE and END\$1DATE columns, insert rows that include null values, then apply an NVL expression to the two columns.

```
create table datetable (start_date date, end_date date);           
insert into datetable values ('2008-06-01','2008-12-31');
insert into datetable values (null,'2008-12-31');
insert into datetable values ('2008-12-31',null);
```

```
select nvl(start_date, end_date)
from datetable
order by 1;
               
coalesce
------------
2008-06-01
2008-12-31
2008-12-31
```

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;
```

For the following example queries, you create a table with sample hotel-booking information and insert several rows. Some records contain null values.

```
create table booking_info (booking_id int, booking_code character(8), check_in date, check_out date, funds_collected numeric(12,2));
```

Insert the following sample data. Some records don't have a `check_out` date or `funds_collected` amount.

```
insert into booking_info values (1, 'OCEAN_WV', '2023-02-01','2023-02-03',100.00);
insert into booking_info values (2, 'OCEAN_WV', '2023-04-22','2023-04-26',120.00);
insert into booking_info values (3, 'DSRT_SUN', '2023-03-13','2023-03-16',125.00);
insert into booking_info values (4, 'DSRT_SUN', '2023-06-01','2023-06-03',140.00);
insert into booking_info values (5, 'DSRT_SUN', '2023-07-10',null,null);
insert into booking_info values (6, 'OCEAN_WV', '2023-08-15',null,null);
```

The following query returns a list of dates. If the `check_out` date isn't available, it lists the `check_in` date.

```
select coalesce(check_out, check_in)
from booking_info
order by booking_id;
```

The results are the following. Note that the last two records show the `check_in` date.

```
coalesce
------------
2023-02-03
2023-04-26	
2023-03-16	
2023-06-03	
2023-07-10	
2023-08-15
```

If you expect a query to return null values for certain functions or columns, you can use an NVL expression to replace the nulls with some other value. For example, aggregate functions, such as SUM, return null values instead of zeroes when they have no rows to evaluate. You can use an NVL expression to replace these null values with `700.0`. Instead of `485`, the result of summing the `funds_collected` is `1885` because two rows that have null are replaced with `700`.

```
select sum(nvl(funds_collected, 700.0)) as sumresult from booking_info;
               
sumresult
------
 1885
```

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

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

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

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

## Arguments
<a name="r_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="r_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>

[DECODE function](r_DECODE_expression.md) can be used in a similar way to NVL2 when the *expression* and *search* parameters are both null. The difference is that for DECODE, the return will have both the value and the data type of the *result* parameter. In contrast, 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="r_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	(906) 632-4407
Caldwell Acevedo 	Nunc.sollicitudin@Duisac.ca
Quinn Adams		  vel@adipiscingligulaAenean.com
Kamal Aguilar		quis@vulputaterisusa.com
Samson Alexander	 hendrerit.neque@indolorFusce.ca
Hall Alford		  ac.mattis@vitaediamProin.edu
Lane Allen		   et.netus@risusDonec.org
Xander Allison	   ac.facilisis.facilisis@Infaucibus.com
Amaya Alvarado	   dui.nec.tempus@eudui.edu
Vera Alvarez		 at.arcu.Vestibulum@pellentesque.edu
Yetta Anthony		enim.sit@risus.org
Violet Arnold		ad.litora@at.com
August Ashley		consectetuer.euismod@Phasellus.com
Karyn Austin		 ipsum.primis.in@Maurisblanditenim.org
Lucas Ayers		  at@elitpretiumet.com
```

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

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

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

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

## Arguments
<a name="r_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. The default column name of the NULLIF result is the column name of the first expression.

## Examples
<a name="r_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)
```

You can use NULLIF to ensure that empty strings are always returned as nulls. In the example below, the NULLIF expression returns either a null value or a string that contains at least one character.

```
insert into category
values(0,'','Special','Special');

select nullif(catgroup,'') from category
where catdesc='Special';

catgroup
----------
null
(1 row)
```

NULLIF ignores trailing blanks. If a string is not empty but contains blanks, NULLIF still returns null:

```
create table nulliftest(c1 char(2), c2 char(2));

insert into nulliftest values ('a','a ');

insert into nulliftest values ('b','b');


select nullif(c1,c2) from nulliftest;
c1
------
null
null
(2 rows)
```