Home arrow Oracle arrow Using the IN and BETWEEN Operators on Tables

Using the IN and BETWEEN Operators on Tables

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).

  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



How to use the IN operator

Figure 3-13 shows how to code a WHERE clause that uses the IN operator. When you use this operator, the value of the test expression is compared with the list of expressions in the IN phrase. If the test expression is equal to one of the expressions in the list, the row is included in the query results. This is illustrated by the first example in this figure, which will return all rows whose terms_id column is equal to 1, 3, or 4.

You can also use the NOT operator with the IN phrase to test for a value thatís not in a list of expressions. This is illustrated by the second example in this figure. In this case, only those vendors who are not in California, Nevada, or Oregon are retrieved.

If you look at the syntax of the IN phrase shown at the top of this figure, youíll see that you can code a subquery in place of a list of expressions. Subqueries are a powerful tool that youíll learn about in chapter 6. For now, though, you should know that a subquery is simply a SELECT statement within another statement. In the third example in this figure, for instance, a subquery is used to return a list of vendor_id values for vendors who have invoices dated May 1, 2008. Then, the WHERE clause retrieves a vendor row only if the vendor is in that list. Note that for this to work, the subquery must return a single column, in this case, vendor_id.

The syntax of the WHERE clause with the IN operator

WHERE test_expression [NOT] IN ({subquery|expression_1 [, expression_2]...})

Examples of the IN operator

The IN operator with a list of numeric literals

WHERE terms_id IN (1, 3, 4)

The IN operator preceded by NOT

WHERE vendor_state NOT IN ('CA', 'NV', 'OR')

The IN operator with a subquery

WHERE vendor_id IN
(SELECT vendor_id
FROM invoices
WHERE invoice_date = '01-MAY-2008')


  1. You can use the IN operator to test whether an expression is equal to a value in a list of expressions. Each of the expressions in the list must evaluate to the same type of data as the test expression.
  2. The list of expressions can be coded in any order without affecting the order of the rows in the result set.
  3. You can use the NOT operator to test for an expression thatís not in the list of expressions.
  4. You can also compare the test expression to the items in a list returned by a subquery as illustrated by the third example above. Youíll learn more about coding subqueries in chapter 6.

--------------------------------------------Figure 3-13 How to use the IN operator

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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- 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: