BETWEEN range condition - Amazon Redshift

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.