Whereas the IN keyword help people to limit the selection criteria to one or more discrete values, the BETWEEN keyword allows for selecting a range. The syntax for the BETWEEN clause is as follows:
SELECT "column_name"
FROM "table_name"
WHERE "column_name" BETWEEN 'value1' AND 'value2'
This will select all rows whose column has a value between 'value1' and 'value2'.
For example, we may wish to select view all sales information between January 6, 1999, and January 10, 1999, in Table Store_Information,
Table Store_Information
store_name | Sales | Date |
Los Angeles | $1500 | Jan-05-1999 |
San Diego | $250 | Jan-07-1999 |
San Francisco | $300 | Jan-08-1999 |
Boston | $700 | Jan-08-1999 |
we key in,
SELECT *
FROM Store_Information
WHERE Date BETWEEN 'Jan-06-1999' AND 'Jan-10-1999'
Note that date may be stored in different formats in different databases. This tutorial simply choose one of the formats.
Result:
store_name | Sales | Date |
San Diego | $250 | Jan-07-1999 |
San Francisco | $300 | Jan-08-1999 |
Boston | $700 | Jan-08-1999 |