HomeOracle Page 2 - Using the IN and BETWEEN Operators on Tables
How to use the BETWEEN operator - Oracle
In this seventh part of a nine-part series, you will learn how to use the IN and BETWEEN operators, along with the SELECT statement, to retrieve data from a single table. This article is excerpted from chapter three of the book Murach's Oracle SQL and PL/SQL, written by Joel Murach (Murach Publishing; ISBN: 9781890774509).
Figure 3-14 shows how to use the BETWEEN operator in a WHERE clause. When you use this operator, the value of a test expression is compared to the range of values specified in the BETWEEN phrase. If the value falls within this range, the row is included in the query results.
The first example in this figure shows a simple WHERE clause that uses the BETWEEN operator. It retrieves invoices with invoice dates between May 1, 2008 and May 31, 2008. Note that the range is inclusive, so invoices with invoice dates of May 1 and May 31 are included in the results.
The second example shows how to use the NOT operator to select rows that are not within a given range. In this case, vendors with zip codes that aren’t between 93600 and 93799 are included in the results.
The third example shows how you can use a calculated value in the test expression. Here, the payment_total and credit_total columns are subtracted from the invoice_total column to give the balance due. Then, this value is compared to the range specified in the BETWEEN phrase.
The last example shows how you can use calculated values in the BETWEEN phrase. Here, the first value selects the function SYSDATE (which represents the current date), and the second value is SYSDATE plus 30 days. So the query results will include all those invoices that are due between the current date and 30 days from the current date.
However, please note the warning about date comparisons in this figure. In particular, an invoice-date of May 31, 2008 at 2:00 PM isn’t less than or equal to “31-May-2008”, and it isn’t between “01-May-2008” and “31-May-2008”. To learn more about date comparisons, please read chapter 8.
The syntax of the WHERE clause with the BETWEEN operator
WHERE test_expression [NOT] BETWEEN begin_expression AND end_expression
Examples of the BETWEEN operator
The BETWEEN operator with literal values
WHERE invoice_date BETWEEN '01-MAY-2008' AND '31-MAY-2008'
The BETWEEN operator preceded by NOT
WHERE vendor_zip_code NOT BETWEEN 93600 AND 93799
The BETWEEN operator with a test expression coded as a calculated value
WHERE invoice_total - payment_total - credit_total BETWEEN 200 AND 500
The BETWEEN operator with the upper and lower limits coded as calculated values
WHERE invoice_due_date BETWEEN SYSDATE AND (SYSDATE + 30)
You can use the BETWEEN operator to test whether an expression falls within a range of values. The lower limit must be coded as the first expression, and the upper limit must be coded as the second expression. Otherwise, the result set will be empty.
The two expressions used in the BETWEEN operator for the range of values are inclusive. That is, the result set will include values that are equal to the upper or lower limit.
You can use the NOT operator to test for an expression that’s not within the given range.
Warning about date comparisons
All columns that have the DATE data type include both a date and time, and so does the value returned by the SYSDATE function. But when you code a date literal like “31-May-2008”, the time defaults to 00:00:00 on a 24-hour clock, or 12:00 AM (midnight). As a result, a date comparison may not yield the results you expect. For instance, May 31, 2008 at 2:00 PM isn’t between “01-May-2008” and “31-May-2008”.
To learn more about date comparisons, please see chapter 8.
--------------------------------------------Figure 3-14 How to use the BETWEEN operator
Please check back regularly for the next part of this series.