Product Documentation

SQL Reference Guide

Previous Topic

Next Topic

Conditional Expressions

Conditional expressions are a subset of scalar functions that generate different results depending on the value of their arguments. They provide some of the flexibility of traditional programming constructs to allow expressions to return alternate results depending on the value of their arguments.

The following scalar functions provide support for conditional expressions. Refer to Scalar Functions for a description of each function.


CASE is the most general conditional expression. It specifies a series of search conditions and associated expressions. FairCom DB SQL returns the value specified by the first expression whose associated search condition evaluates as true. If none of the expressions evaluate as true, the CASE expression returns a null value (or the value of some other default expression if the CASE expression includes the ELSE clause).

All the other conditional expressions can also be expressed as CASE expressions.


DECODE provides a subset of the functionality of CASE that is compatible with Oracle SQL syntax. DECODE is not SQL-92 compatible.


NULLIF substitutes a null value for an expression if it is equal to a second expression.


COALESCE specifies a series of expressions. FairCom DB SQL returns the first expression whose value is not null. If all the expressions evaluate as null, COALESCE returns a null value.


IFNULL substitutes a specified value if an expression evaluates as null. If the expression is not null, IFNULL returns the value of the expression.