BETWEEN predicate

A BETWEEN predicate determines whether a value is:

The predicate evaluates to TRUE if a value falls within the specified range.

SQL syntax

Expression1 [NOT] BETWEEN Expression2 AND Expression3

Parameters

Parameter	Description
`Expression1`, `Expression2`, `Expression3`	The syntax for expressions is defined in "Expression specification". Both numeric and non-numeric expressions are allowed in BETWEEN predicates, but all expressions must be compatible with each other.

Description

BETWEEN evaluates to FALSE and NOT BETWEEN evaluates to TRUE if the second value is greater than the third value.
Consult the following table if either Expression2 or Expression3 is NULL for BETWEEN or NOT BETWEEN:

Expression2 Expression3 BETWEEN NOT BETWEEN

<= Expression1 NULL NULL NULL

> Expression1 NULL FALSE TRUE

NULL >= Expression1 NULL NULL

NULL < Expression1 NULL NULL
Expression2 and Expression3 constitute a range of possible values for which Expression2 is the lowest possible value and Expression3 is the highest possible value within the specified range. In the BETWEEN predicate, the low value must be specified first.

Comparisons are conducted as described in "Comparison predicate".
The BETWEEN predicate is not supported for NCHAR types.

Expression2	Expression3	BETWEEN	NOT BETWEEN
<= `Expression1`	NULL	NULL	NULL
> `Expression1`	NULL	FALSE	TRUE
NULL	>= `Expression1`	NULL	NULL
NULL	< `Expression1`	NULL	NULL

Examples

Parts sold for under $250.00 and over $1500.00 are discounted 25 percent.

UPDATE Purchasing.Parts 
SET SalesPrice = SalesPrice * 0.75
WHERE SalesPrice NOT BETWEEN 250.00 AND 1500.00;