BETWEEN / NOT BETWEEN
Tests whether an expression value is between two other expression values.
BETWEEN and NOT BETWEEN are ANSI SQL:2011 compliant.
The BETWEEN test is satisfied if the following condition is true.
expression_2 <= expression_1 <= expression_3
If the BETWEEN test fails, no rows are returned.
The BETWEEN test is treated as two separate logical comparisons.
expression_1 >= expression_2 AND expression_1 <= expression_3.
This expression …
Is equivalent to …
x BETWEEN y AND z
((x >= y) AND (x <=z))
Note that because expression_1 is actually evaluated twice, using a nondeterministic function, such as RANDOM, can produce unexpected results.
The following example uses a search condition in a HAVING clause to select from the Employee table those departments with the number 100, 300, 500, or 600, and with a salary average of at least $35,000 but not more than $55,000:
WHERE DeptNo IN (100,300,500,600)
GROUP BY DeptNo
HAVING AVG(Salary) BETWEEN 35000 AND 55000 ;