It is determined whether the row lies within a range specified its lower and upper limits.
{ row_1 [NOT] BETWEEN row_2 AND row_3 |
vector_column [NOT] BETWEEN expression AND expression }
row ::= { (expression ,...) | expression | subquery_2 }
vector_column ::= [table.] { column[min..max] | column(min..max) }
row
Each expression in row must be atomic. The row consists of the expression values in the order specified. A single expression therefore returns a row with one column.
subquery must return a table without multiple columns and with at most one row. This row is the operand. If the table returned is empty, the operand is a row with the NULL value in each column.
All three rows must have the same number of columns; corresponding columns must have compatible data types (see section "Compatibility between data types").
vector_column
A multiple column with special rules for the result. The column specification may not be an external reference.
expression
The values must be atomic and their data types must be compatible with the data type of the vector_column occurrences ( section "Compatibility between data types").
Result
row_1 BETWEEN row_2 AND row_3 is identical to:
(row_1 >= row_2) AND (row_1 <= row_3)
row_1 NOT BETWEEN row_2 AND row_3is identical to:
NOT ( row_1 BETWEEN row_2 AND row_3)
vector_column [NOT] BETWEEN expression AND expression
The range query is performed for each occurrence of vector_column.
The individual results are combined with OR.
Example
If X is a multiple column with 3 elements, the range query X[1..3] BETWEEN 13 AND 20 is equivalent to the following range queries:
X[1] BETWEEN 13 AND 20 OR X[2] BETWEEN 13 AND 20 OR X[3] BETWEEN 13 AND 20
Examples
BETWEEN predicate with numeric range:
Select all the items from the ITEMS table whose price is between 0 and 10 Euros, which include the item name in the output.
 | SELECT item_num, item_name, price FROM items WHERE price BETWEEN 0.00 AND 10.00 item_num item_name price 210 Front hub 5.00 220 Back hub 5.00 230 Rim 10.00 240 Spoke 1.00 500 Screw M5 1.10 501 Nut M5 0.75 |
BETWEEN predicate with range of dates:
Select all the orders placed in December 2013 from the ORDERS table, which include the order number, customer number, order date and order text in the output:
SELECT order_num, cust_num, order_text, order_date FROM orders
WHERE order_date BETWEEN DATE'2013-12-01' AND DATE'2013-12-31'
order_ cust_ order_text order_date
num num
210 106 Customer administration 2013-12-13
211 106 Database design customers 2013-12-30
BETWEEN predicate with a host variable:
MINIMUM is a host variable. The comparison returns true if the product of SERVICE_PRICE*SERVICE_TOTAL (price per service unit times number of service units) is outside the specified range. It returns false if the product is within the range. The comparison returns unknown if the value of SERVICE_PRICE or
SERVICE_TOTAL is unknown.
service_price*service_total NOT BETWEEN :MINIMUM AND 2000