Range and Geographical Searches Using BETWEEN

The BETWEEN operator allows you to select rows of data in a given column if data in a given column contain values within a range. The general form of this operator is:

expression  [NOT]  BETWEEN  lower value  AND  upper value

The condition is true if the expression is greater than or equal to the lower value and less than or equal to the upper value. If the NOT operator is used, the row is retrieved if the expression is less than the lower value or greater than the upper value.

Example: Let's find all departments whose budgets are between $15,000 and $25,000.

If you enter the statement:

SELECT  DNAME, BUDGET
     FROM    DEPARTMENT
     WHERE   BUDGET  BETWEEN  15000  AND  25000 ;

DNAME                              BUDGET
  Product Marketing and Sales        25000
  Corporate Library                  18500
  Information Systems Management     22500

The name of each department whose budget is between $15,000 and $25,000 is retrieved. The limits include any budget of $15,000 and of $25,000; thus the Product Marketing and Sales Department with a budget matching the upper limit has been included.

The AND logical operator can also be used to form a query that selects values from a range. A query similar to the last example would look like the following.

If you enter the following statement:

SELECT  DNAME, BUDGET
     FROM    DEPARTMENT
     WHERE   BUDGET >= 15000  AND  BUDGET <= 25000 ;

DNAME                              BUDGET
  Product Marketing and Sales        25000
  Corporate Library                  18500
  Information Systems Management     22500

Notice that the results are identical to the output in example where BETWEEN was used in the WHERE clause.

The BETWEEN operator can be modified with the logical operator NOT so that rows outside a range will be selected.

Example: List the names of all departments who do not have a budget in the range of $15,000 to $25,000.

If you enter the statement:

SELECT  DNAME, BUDGET
     FROM    DEPARTMENT
     WHERE   BUDGET  NOT  BETWEEN  15000  AND  25000 ;

DNAME                                BUDGET
  Corporate Legal Support              28000
  Supplies and Procurement             10500
  Customer Support and Service         11000
  Manufacturing                        32000
  Research and Development             27500
  Strategic Planning and Intelligence  28500

This statement retrieves the names of all departments with budgets lower than $15,000 or higher than $25,000.