BETWEEN condition determines whether the value of one expression is in an interval defined by two other expressions.
All three expressions must be numeric, character, or datetime expressions. In SQL, it is possible that
expr1 will be evaluated more than once. If the
BETWEEN expression appears in PL/SQL,
expr1 is guaranteed to be evaluated only once. If the expressions are not all the same datatype, then Oracle Database implicitly converts the expressions to a common datatype. If it cannot do so, then it returns an error.
Note:The implicit datatype conversion rules for PL/SQL are different from those for SQL. For more information, see Oracle Database PL/SQL Language Reference.
See Also:"Implicit Data Conversion" for more information on SQL datatype conversion
The value of
expr1 NOT BETWEEN expr2 AND expr3
is the value of the expression
NOT (expr1 BETWEEN expr2 AND expr3)
And the value of
expr1 BETWEEN expr2 AND expr3
is the value of the boolean expression:
expr2 <= expr1 AND expr1 <= expr3
expr2, then the interval is empty. If
NULL, then the result is
expr1 is not
NULL, then the value is
FALSE in the ordinary case and
TRUE when the keyword
NOT is used.
The boolean operator
AND may produce unexpected results. Specifically, in the expression
x AND y, the condition
x IS NULL is not sufficient to determine the value of the expression. The second operand still must be evaluated. The result is
FALSE if the second operand has the value
NULL otherwise. See "Logical Conditions" for more information on