BETWEEN Predicate

A BETWEEN predicate determines whether a value is:

  • Greater than or equal to a second value

and:

  • Less than or equal to a third value

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

See "Expression Specification" for information on the syntax. 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.

    See "Comparison Predicate" for information on comparisons.

  • The BETWEEN predicate is not supported for NCHAR types.

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;