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.

• Geographical Searches with BETWEEN