BETWEEN Predicate
Function
This predicate checks to see if a specified value lies within the defined range and returns a tri-state boolean result.
Invocation
One of the six predicates which constitute a search term.
Syntax
expression_1 [NOT] BETWEEN expression_2 AND expression_3
where
expression_1 | A valid expression as described in the section Expressions. |
NOT | An operator which negates the result of the predicate. |
expression_2 & expression_3 | Each is a valid expression as described in the section Expressions. |
AND | Simply separates expressions 2 and 3. Do not confuse with use as a boolean operator. |
Description
The BETWEEN predicate checks if the value specified by expression 1 lies within the range specified by the values derived from expression 2 and expression 3 respectively. It is equivalent to the following pair of COMPARISON predicates:
expression1 BETWEEN expression2 AND expression3
(expression1 >= expression2) AND (expression1 <= expression3);
CONNX processes the BETWEEN predicate as if it were expressed in this form.
The NOT operator negates the result of the boolean expression.
All expressions must have comparable data types. Should either of the expressions evaluate to NULL, the predicate returns the tri-state value of unknown.
Example
The following example selectes the cruises that have a cruise price between and including 800 and 2000.
SELECT cruise_id
FROM cruise
WHERE cruise_price BETWEEN 800 and 2000 ;