Home arrow Oracle arrow 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).

TABLE OF CONTENTS:
  1. Using the IN and BETWEEN Operators on Tables
  2. How to use the BETWEEN operator
By: Murach Publishing
Rating: starstarstarstarstar / 0
August 25, 2011

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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)

Description

  1. 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.
  2. 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.
  3. You can use the NOT operator to test for an expression that’s not within the given range.

Warning about date comparisons

  1. 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”.
  2. 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.



 
 
>>> More Oracle Articles          >>> More By Murach Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: