Documentation Source Text

that with BETWEEN, the <i>x</i> expression is only evaluated once.)^
^The precedence of the BETWEEN operator is the same as the precedence
as operators <b>==</b> and <b>!=</b> and <b>LIKE</b> and groups left to right.

<tcl>hd_fragment case {CASE expression}</tcl>
<h3>The CASE expression</h3>
<p>A CASE expression serves a role similar to IF-THEN-ELSE in other
programming languages.  ^WHEN expressions are evaluated from left to
right until one is found that is true, at which point the corresponding
THEN term becomes the result.  ^If no WHEN expression is true then the ELSE 
clause determines the result or the result is NULL if there is no ELSE clause.
</p>

<p>The optional expression that occurs in between the CASE keyword and the
first WHEN keyword is the "base" expression.  ^There are two basic forms

of a CASE expression: those with and without a base expression.

^In a CASE without a base expression, each WHEN expression is evaluated



as a boolean and the overall result is determined by first WHEN expression
that is true.




^In a CASE with a base expression, the base expression is evaluated just
once and the result is compared against each WHEN expression until a 
match is found.








^When comparing a base expression against a WHEN expression, the same
collating sequence, affinity, and NULL-handling rules apply as if the
base expression and WHEN expression are respectively the left- and
right-hand operands of an <big><b>=</b></big> operator.</p>



<p>^(Assuming the subexpressions have no side-effects, the following
two expressions are equivalent:</p>







<ul>
<li>CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END
<li>CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END
</ul>)^

<p>^The only difference between the two CASE expressions shown above
is that the <i>x</i> expression is evaluated
exactly once in the first example but might be evaluated multiple times
in the second.</p>

<p>^A NULL result is considered false when evaluating WHEN terms.
^If the base expression is NULL then the result of the CASE is the
result of the ELSE expression if it exists, or NULL if the ELSE clause
is omitted.</p>

<tcl>hd_fragment in_op {IN operator} {NOT IN operator}</tcl>
<h3>The IN and NOT IN operators</h3>
<p>^The IN and NOT IN operators take a single scalar operand on the
left and a vector operand on the right
formed by an explicit list of zero or more scalars or by a 
single subquery.

that with BETWEEN, the <i>x</i> expression is only evaluated once.)^
^The precedence of the BETWEEN operator is the same as the precedence
as operators <b>==</b> and <b>!=</b> and <b>LIKE</b> and groups left to right.

<tcl>hd_fragment case {CASE expression}</tcl>
<h3>The CASE expression</h3>
<p>A CASE expression serves a role similar to IF-THEN-ELSE in other
programming languages.  





<p>The optional expression that occurs in between the CASE keyword and the

first WHEN keyword is called the "base" expression. ^There are two basic forms
of the CASE expression: those with a base expression and those without.

<p>^In a CASE without a base expression, each WHEN expression is evaluated
and the result treated as a boolean, starting with the leftmost and continuing
to the right. ^The result of the CASE expression is the evaluation of the THEN
expression that corresponds to the first WHEN expression that evaluates to
true. ^Or, if none of the WHEN expressions evaluate to true, the result of
evaluating the ELSE expression, if any. ^If there is no ELSE expression and
none of the WHEN expressions are true, then the overall result is NULL.

<p>^A NULL result is considered untrue when evaluating WHEN terms.

<p>^In a CASE with a base expression, the base expression is evaluated just
once and the result is compared against the evaluation of each WHEN 

expression from left to right. ^The result of the CASE expression is the 
evaluation of the THEN expression that corresponds to the first WHEN
expression for which the comparison is true. ^Or, if none of the WHEN
expressions evaluate to a value equal to the base expression, the result
of evaluating the ELSE expression, if any. ^If there is no ELSE expression and
none of the WHEN expressions produce a result equal to the base expression,
the overall result is NULL.

<p>^When comparing a base expression against a WHEN expression, the same
collating sequence, affinity, and NULL-handling rules apply as if the
base expression and WHEN expression are respectively the left- and
right-hand operands of an <big><b>=</b></big> operator.</p> ^If the base 
expression is NULL then the result of the CASE is always the result 
of evaluating the ELSE expression if it exists, or NULL if it does not.



<p>^Both forms of the CASE expression use lazy, or short-circuit, 
evaluation.

<p>^(The only difference between the following two CASE expressions is that 
the <i>x</i> expression is evaluated exactly once in the first example but 
might be evaluated multiple times in the second:

<ul><pre>
<li>CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END
<li>CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END
</pre></ul>)^











<tcl>hd_fragment in_op {IN operator} {NOT IN operator}</tcl>
<h3>The IN and NOT IN operators</h3>
<p>^The IN and NOT IN operators take a single scalar operand on the
left and a vector operand on the right
formed by an explicit list of zero or more scalars or by a 
single subquery.