BETWEEN range condition - AWS Clean Rooms
SyntaxExamples

BETWEEN range condition

A BETWEEN condition tests expressions for inclusion in a range of values, using the keywords BETWEEN and AND.

Syntax

expression [ NOT ] BETWEEN expression AND expression

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.