Home

SQL Operators: BETWEEN
     

Introduction

 

If you have a logical range of values and you want to know if a certain value is contained in that range, you can use add a BETWEEN operator to a WHERE statement. The BETWEEN operator is combined with AND to get a list of records between two values. The basic formula of this operator is:

 
WHERE Expression BETWEEN Start AND End

The Expression placeholder is usually the name of the column whose values you want to examine. The Start factor is the starting value of the range to consider. the end factor is the highest value to consider in the range. After this condition is executed, it produces the list of values between Start and End.

To create a BETWEEN expression in the Table window, under the Filter column that corresponds to the field on which you want to set the condition, type the BETWEEN expression. Here is an example that specifies the price range a customer wants to consider for a house:

BETWEEN

As stated already, in SQL, you use the BETWEEN operator to create this type of condition. Here is an example:

SELECT PropertyType, City, Bedrooms, Stories, MarketValue
FROM Properties
WHERE MarketValue BETWEEN 320000 AND 450000

To make the condition easy to ready, you should include the whole BETWEEN statement in parentheses. Here is an example:

BETWEEN

When creating your BETWEEN statement, you can use any of the SQL features we have studied so far. To create a complex expression, you can combine many (or all) of the conditions we have reviewed so far. For example, you may have a customer who is considering purchasing a townhouse around Silver Spring but he does not want to sp} more than 400000. Because the customer wants the townhouse in the Silver Spring area, you will not consider the City column. You remember that Silver Spring contains many ZIP Codes from 20800 to 20999. To get the list, you would formula your SELECT statement as follows:

SELECT PropertyNumber AS [Prop #],
       PropertyType AS Type,
       YearBuilt AS [Year Built],
       City,
       ZIPCode AS [ZIP Code],
       Bedrooms AS Beds,
       Bathrooms AS Baths,
       MarketValue AS Value
FROM Properties
WHERE (PropertyType = N'Townhouse') AND
      (MarketValue < 400000) AND
      (ZIPCode = (SELECT ZIPCode
      WHERE ZIPCode BETWEEN N'20500' AND N'21000'));

If you are working in a Windows application, you can assign the expression to the Filter property of the binding source.

   
 
Home Copyright © 2010-2016, FunctionX