Conditional Expression in SQL

• Provide the use of IF-THEN-ELSE logic within a SQL statement.
• Use two methods:
• CASE expression.
• DECODE function.

The CASE Expression

• Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement.

Syntax:

CASE expr WHEN comparison_expr1 THEN return_expr1

                    [ WHEN comparison _expr2 THEN return_expr2

                     WHEN comparison_exprn THEN return_exprn

                     ELSE else_expr ]

END

• In a simple CASE expression, Oracle searches for the first WHEN --- THEN pair for which expr is equal to comparison_expr and returns return_expr. 
• If none of the WHEN --- THEN pairs meet this condition, and ELSE clause exists, then Oracle returns else_expr.
• Otherwise, Oracle returns null.
• You can't specify the literal NULL for all the return_exprs and the else_expr.

Example:

The DECODE Function

Facilitates conditional inquiries by doing the work of a CASE or IF-THEN-ELSE statement:

Syntax:

DECODE(col|expression, search1, result1

                                        [,search2,result2,...]

                                        [,default])

NOTE:

• The DECODE function decodes an expression in a way similar to the IF-THEN-ELSE logic used in various languages. 
• The DECODE function decodes expression after comparing it to each search value.
• If the expression is the same as search, result is returned.
• If the default value is omitted, a null value is returned where a search value does not match any of the result value.

Example: