The BETWEEN operator is used in the WHERE clause to help narrow down the search process. It defines parameters within which the statement affects the table in question. The data can be in the form of text, numbers, or dates.

SELECT column
FROM table
WHERE column
BETWEEN value_1 AND value_2;

BETWEEN can help to narrow down the data a script will need to process. In PHP, for example, the entire table could be downloaded and then the appropriate data separated out within the script. Using BETWEEN is faster and reduces the amount of work a server or script needs to do. It is generally best to try to do as much work in the SQL query as possible.

In a Monopoly game, a player wishes to find out how many properties she owns that cost between $120.00 and $300.00. Here is what she currently has in her my_prop table:

1Connecticut Avelt blue10050
2St. James Placeorange180100
3Kentucky Avered220150
4Board Walkblue400200

She makes the following query:

SELECT street_name
FROM my_prop
WHERE prop_price
BETWEEN 120 AND 300;

The result in this case is contained in two rows:

2St. James Placeorange180100
3Kentucky Avered220150

If there are no rows meeting the requirements an empty result set will be returned. Also, it should be noted that some SQL languages will include rows equivalent to the parameters, while others will not.


Interesting Pages