if

Implement a conditional selection

Syntax

if(conditional_expr,value_expr1,value_expr2)

Arguments

conditional_expr	The conditional expression.
value_expr1	The expression to be evaluated and returned if the conditional expression evaluates to TRUE.
value_expr2	The expression to be evaluated and returned if the conditional expression evaluates to FALSE.

Description

This function conditionally evaluates one of two expressions for each row of the SELECT statement in which it is used. The expression to be evaluated and returned is based on the value of the specified conditional expression for each row. If the conditional expression evaluates to TRUE, the if evaluates and retrieves the value of the first expression (value_expr1). If the conditional expression evaluates to FALSE, the function evaluates and returns the value of the second expression (value_expr2). Both expressions must return values of identical data types.

Example

select quantity, prod_id, prod_desc,
 	if(quantity > 20, .8*price, if(quantity > 5, .9*price, price)) "PRICE"
 	from item natural join product;

update sales_order
 	set tax = if(state="WA", amount*0.085, if(state="CO", amount*0.062, 0.0))
 	where state in ("CA","WA");

select 
 	sum(if(prod_id=10320, quantity, 0)) "386/20", 
 	sum(if(prod_id=10333, quantity, 0)) "386/33",
 	sum(if(prod_id=10433, quantity, 0)) "486/33",
 	sum(if(prod_id=10450, quantity, 0)) "486/50",
from item;