Range Searching

In order to select data that is within a range of values, the BETWEEN operator is used. The BETWEEN operator allows the selection of rows that contain values within a specified lower and upper limit. The range coded after the word BETWEEN is inclusive.

The lower value must be coded first. The two values in between the range must be linked with the keyword AND. A BETWEEN operator can be used with both character and numeric data types. However, one cannot mix the data types that is the lower value of a range of values from a character column and the other from a numeric column.

Example:

1. Retrieve product_no,description,profit_percent,sell_price from the table product_master where the values contained within the field profit_percent is Between 10 and 20 both inclusive.

SELECT product_no,description,profit_percent,sell_price FROM Product_Master WHERE profit_percent BETWEEN 10 AND 20;

The above select will retrieve all the records from the product_master table where the profit_percent is in between 10 and 20 (both values inclusive).

2. Retrieve product_no,description,profit_percent, and sell_price from the produt_master table where the values contained in the field profit_percent are not between 10 and 15 both inclusive

SELECT product_no,description,profit_percent,sell_price FROM product_master WHERE profit_percent NOT BETWEEN 10 AND 15;

The above select will retrieve all the records from the product_master table expect where the profit_percent is in between 10 and 15 (both values inclusive).

Range Searching