BETWEEN and IN are operations that determine whether an expression's value falls within a specified range or at specified values. It's useful when you have a list of things you want to pull out or mark, but don't want to go through a large database to find them.
The syntax for BETWEEN is:
expr [Not] Between value1 And value2
Or:
SELECT columns FROM tables WHERE column1 between value1 and value2;
BETWEEN is always paired with an AND. In the syntax above, expr is an expression representing the field with the data you're evaluating. For instance, if you're trying to determine whether the median salary of your department falls within a certain range, expr would be replaced with an expression calculating your department's median salary; for evaluating individual salaries, you would use the field name.
Value1 and value2 are the bookends of your range. You would replace these with either numbers or with expressions – for instance, an expression that calculates the median salaries of two other departments.
This SQL operation does not return a number or a table, as most other SQL expressions do; instead, this one returns a True or a False. NOT, of course, evaluates whether expr lies outside of the range you have specified.
Here's an example. The U.S. Post Office charges postage in ranges they will share with you. If you want to have the correct postage, you need to sort your packages by ranges. So you would:
SELECT * FROM shippingtable WHERE zip_code between ziptable.zip1 and ziptable.zip2
This pulls out all the records in your shipping table with zip codes within a certain range; your zip code ranges are included in a separate table in your database, ziptable. This code piece can be used to pull out all like shipping for organized bulk mailing, or it can be used as part of a larger SQL query designed to manage all your shipping. Any SQL statement can include your BETWEEN statement (SELECT, INSERT, UPDATE, or DELETE).
If you were looking for a particular range of customer number, product number, or UPC or other scan bar code, you could look for values within a specified range of numbers, as follows:
SELECT * FROM product WHERE product.UPC_code between 800012125000 AND 800012125100;
This SQL statement will pull UPC codes within a certain value – excellent if you need to put a range of products on sale or change the prices within all of them at a grocery store.
You could perform the same function, if you wished, with
SELECT * FROM product WHERE product.UPC_code >= 800012125000 AND product.UPC_code <=800012125100;
The difference? Using the BETWEEN function instead keeps your code cleaner and easier to understand later.
If you wanted to pull products by dates instead of UPC codes, BETWEEN is good for that as well:
SELECT *
FROM product
WHERE order_date between to_date ('2006/01/01', 'yyyy/mm/dd')
AND to_date ('2006/12/31', 'yyyy/mm/dd');
This pulls all items ordered within a certain timeframe, if you keep
your order dates with a product.
If you wanted to run an exclusionary SQL statement instead of an inclusionary,
you would use the NOT operator in conjunction with BETWEEN.
SELECT * FROM product WHERE product.UPC_code NOT BETWEEN 800012125000 AND 800012125100;
This would return all products with UPC values NOT between the specified UPC code values.
You can't use wild card characters with BETWEEN…AND.
This function is very useful for classifying items in two different ways: On Sale or Not On Sale, for instance, for UPCs, or Local and Nonlocal for telephone numbers or postal zip codes. For instance, the following function will return Local or Nonlocal
SELECT IIf(PostalCode Between 40200 And 40299, "Local", "Nonlocal")
This will mark a database's postal codes either Local or Nonlocal; those between 40200 and 40299 are marked local, while all others are marked nonlocal.
A related operator to BETWEEN is IN, but instead of looking at a range, IN is used to determine if a value in an expression is equal to one or more of several values specified in a list in the operation. Its syntax is:
expr [Not] In(value1, value2, . . .)
In this example, just as in BETWEEN, expr is the field you're evaluating. Value1, value2, etc. are the values you're comparing it with. Expressions that are found to be equal to one of the values are marked True, and those that aren't are marked False.
For example, if you were running a sale on specified items only, you could identify items on sale by the UPC code:
SELECT * FROM product WHERE UPC_code In (800010992002, 800010921103)
This returns a table including products with those UPC codes only.