BETWEEN

BETWEEN is used to simplify range tests, by returning TRUE when the input is within two boundaries.

Syntax

expr [ NOT ] BETWEEN lower_bound AND upper_bound

Arguments

Parameter Description
expr A general value expression or a literal.
lower_bound, upper_bound Lower and upper bounds, of the same data type as expr

Returns

Returns TRUE when expr is within the bounds, or FALSE otherwise.

Notes

  • expr BETWEEN X AND Y is equivalent to expr >=X AND expr <=Y.
  • The upper boundary must be greater than the lower boundary

Examples

BETWEEN

farm=> SELECT name, num_eyes FROM cool_animals WHERE num_eyes BETWEEN 5 and 8
name           | num_eyes
---------------+---------
Spider         |        8
Starfish       |        5
Praying mantis |        5

NOT BETWEEN

farm=> SELECT name, num_eyes FROM cool_animals WHERE num_eyes NOT BETWEEN 5 and 8
name           | num_eyes
---------------+---------
Human          |        2
Horseshoe crab |       10
Box Jellyfish  |       24
Fox            |        2
Possum         |        2