Product Documentation

Appearance

7.1.2
7.0.2
6.9.3
5.4.17
5.2.31
All platforms

BETWEEN ​

Define a condition that qualifies rows based on a range of numbers, datetime values, or character strings.

expression [ NOT ] BETWEEN [ SYMMETRIC ] expression AND expression

For example:

premdb=# SELECT * FROM match 
WHERE matchday BETWEEN '2013-05-15' AND '2013-05-31' ORDER BY 1;
 seasonid |      matchday       | htid | atid | ftscore | htscore 
----------+---------------------+------+------+---------+---------
      21 | 2013-05-19 00:00:00 |   14 |   67 | 2-1     | 1-1
      21 | 2013-05-19 00:00:00 |   24 |   82 | 1-0     | 1-0
      21 | 2013-05-19 00:00:00 |   25 |   78 | 2-3     | 1-1
      21 | 2013-05-19 00:00:00 |   28 |   51 | 0-1     | 0-0
      21 | 2013-05-19 00:00:00 |   37 |   87 | 1-1     | 0-0
      21 | 2013-05-19 00:00:00 |   40 |   68 | 0-3     | 0-1
      21 | 2013-05-19 00:00:00 |   42 |   88 | 1-0     | 0-0
      21 | 2013-05-19 00:00:00 |   44 |   75 | 5-5     | 1-3
      21 | 2013-05-19 00:00:00 |   45 |   83 | 4-2     | 2-0
      21 | 2013-05-19 00:00:00 |   46 |   52 | 2-2     | 2-1
(10 rows)

BETWEEN SYMMETRIC is a variation on the standard BETWEEN syntax that can swap the range boundaries. The first argument does not have to be less than or equal to the second argument. For example, the first query below finds no matches, but the second query swaps the range boundaries and finds 11 rows:

premdb=# select * from team where teamid between 60 and 40;
 teamid | htid | atid | name | nickname | city | stadium | capacity 
--------+------+------+------+----------+------+---------+----------
(0 rows)

premdb=# select * from team where teamid between symmetric 60 and 40;
 teamid | htid | atid |          name           | nickname |     city      |     stadium      | capacity 
--------+------+------+-------------------------+----------+---------------+------------------+----------
    40 |   41 |   90 | Swindon Town            | Robins   | Swindon       | County Ground    |    15728
    41 |   42 |   91 | Tottenham Hotspur       | Spurs    | London        | White Hart Lane  |    36284
    42 |   43 |   92 | Watford                 | Hornets  | Watford       | Vicarage Road    |    21577
    43 |   44 |   93 | West Bromwich Albion    | Baggies  | Birmingham    | The Hawthorns    |    27000
    44 |   45 |   94 | West Ham United         | Hammers  | London        | Upton Park       |    35016
    45 |   46 |   95 | Wigan Athletic          | Latics   | Wigan         | DW Stadium       |    25138
    46 |   47 |   96 | Wimbledon               | Dons     | London        | Selhurst Park    |    26255
    47 |   48 |   97 | Wolverhampton Wanderers | Wolves   | Wolverhampton | Molineux Stadium |    31700
    48 |   49 |   98 |                         |          |               |                  |        0
    49 |   50 |   99 |                         |          |               |                  |        0
    50 |   51 |  100 |                         |          |               |                  |        0
(11 rows)