[NOT] BETWEEN

Purpose

This tests whether an expression is situated between two values.

Syntax

Usage Notes

  • ASYMMETRIC is used by default if neither ASYMMETRIC nor SYMMETRIC is specified.

  • If SYMMETRIC is specified, the order of the specified limits will not matter.

  • a BETWEEN b AND c and a BETWEEN ASYMMETRIC b AND c are both equivalent to b <= a AND a <= c.

  • a BETWEEN SYMMETRIC b AND c is equivalent to (a BETWEEN ASYMMETRIC b AND c) OR (a BETWEEN ASYMMETRIC c AND b). This means that the order of the specified limits does not matter for BETWEEN SYMMETRIC. For example, 1 BETWEEN SYMMETRIC 0 AND 3 and 1 BETWEEN SYMMETRIC 3 AND 0 are both true.

  • a NOT BETWEEN b AND c and a NOT BETWEEN ASYMMETRIC b AND c are both equivalent to NOT (a BETWEEN ASYMMETRIC b AND c).

  • a NOT BETWEEN SYMMETRIC b AND c is equivalent to NOT (a BETWEEN SYMMETRIC b AND c).

Example
SELECT 1 BETWEEN 0 AND 3 AS res;

SELECT 1 BETWEEN SYMMETRIC 3 AND 0 AS res; 

SELECT 4 NOT BETWEEN ASYMMETRIC 0 AND 3 AS res; 

SELECT 4 NOT BETWEEN SYMMETRIC 3 AND 0 AS res;