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 ;
the result displayed will be:

  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 ;
the result displayed will still be:

  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 ;
the result displayed will be:

  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.



Copyright © Thunderstone Software     Last updated: Apr 26 2017
Copyright © 2017 Thunderstone Software LLC. All rights reserved.