CASE
expression has two forms.
The “simple” form searches each value
expression from left to right
until it finds one that equals expression
:
result
for the matching value
is returned.
If no match is found, the result
from the ELSE
clause is
returned if it exists, otherwise null is returned. Example:
condition
from left
to right until one is true and returns the matching result
:
result
from the ELSE
clause is
returned if it exists, otherwise null is returned. Example:
CASE
statements that use a slightly
different syntax from the CASE expressions. Specifically note the requirements
for terminating each clause with a semicolon ;
and the usage of END CASE
.
IF
expression has two forms, one supplying only a
true_value
and the other supplying both a true_value
and a
false_value
:
true_value
if condition
is true,
otherwise null is returned and true_value
is not evaluated.
true_value
if condition
is true,
otherwise evaluates and returns false_value
.
The following IF
and CASE
expressions are equivalent:
IF
statements that use a slightly
different syntax from IF
expressions. Specifically note the requirement
for terminating each clause with a semicolon ;
and the usage of END IF
.
value
in the argument list.
Like a CASE
expression, arguments are only evaluated if necessary.
value1
equals value2
, otherwise returns value1
.
NULL
.
In cases where it is preferable that queries produce NULL
or default values
instead of failing when corrupt or invalid data is encountered, the TRY
function may be useful. To specify default values, the TRY
function can be
used in conjunction with the COALESCE
function.
The following errors are handled by TRY
:
TRY
:
NULL
values with TRY
:
TRY
:
TRY
and COALESCE
: