# BETWEEN range condition

A `BETWEEN`

condition tests expressions for inclusion in a range of
values, using the keywords `BETWEEN`

and `AND`

.

## Syntax

expression[ NOT ] BETWEENexpressionANDexpression

Expressions can be numeric, character, or datetime data types, but they must be compatible. The range is inclusive.

## Examples

The first example counts how many transactions registered sales of either 2, 3, or 4 tickets:

`select count(*) from sales where qtysold between 2 and 4; count -------- 104021 (1 row)`

The range condition includes the begin and end values.

`select min(dateid), max(dateid) from sales where dateid between 1900 and 1910; min | max -----+----- 1900 | 1910`

The first expression in a range condition must be the lesser value and the second expression the greater value. The following example will always return zero rows due to the values of the expressions:

`select count(*) from sales where qtysold between 4 and 2; count ------- 0 (1 row)`

However, applying the NOT modifier will invert the logic and produce a count of all rows:

`select count(*) from sales where qtysold not between 4 and 2; count -------- 172456 (1 row)`

The following query returns a list of venues with 20000 to 50000 seats:

`select venueid, venuename, venueseats from venue where venueseats between 20000 and 50000 order by venueseats desc; venueid | venuename | venueseats ---------+-------------------------------+------------ 116 | Busch Stadium | 49660 106 | Rangers BallPark in Arlington | 49115 96 | Oriole Park at Camden Yards | 48876 ... (22 rows)`

The following example demonstrates using BETWEEN for date values:

`select salesid, qtysold, pricepaid, commission, saletime from sales where eventid between 1000 and 2000 and saletime between '2008-01-01' and '2008-01-03' order by saletime asc; salesid | qtysold | pricepaid | commission | saletime --------+---------+-----------+------------+--------------- 65082 | 4 | 472 | 70.8 | 1/1/2008 06:06 110917 | 1 | 337 | 50.55 | 1/1/2008 07:05 112103 | 1 | 241 | 36.15 | 1/2/2008 03:15 137882 | 3 | 1473 | 220.95 | 1/2/2008 05:18 40331 | 2 | 58 | 8.7 | 1/2/2008 05:57 110918 | 3 | 1011 | 151.65 | 1/2/2008 07:17 96274 | 1 | 104 | 15.6 | 1/2/2008 07:18 150499 | 3 | 135 | 20.25 | 1/2/2008 07:20 68413 | 2 | 158 | 23.7 | 1/2/2008 08:12`

Note that although BETWEEN's range is inclusive, dates default to having
a time value of 00:00:00. The only valid January 3 row for
the sample query would be a row with a saletime of `1/3/2008 00:00:00`

.