Home arrow Oracle arrow Page 5 - Mastering the WHERE Clause

Membership Conditions and Range Conditions - Oracle

Gain the full power of SQL to write queries in an Oracle environment with this updated book (new information on Oracle 10g). This chapter focuses on the role of the WHERE clause in SQL statements and the various options available when building a WHERE clause. (Mastering Oracle SQL by Sanjay Mishra and Alan Beaulieu, O'Reilly, ISBN: 596006322.)

  1. Mastering the WHERE Clause
  2. WHERE to the Rescue
  3. WHERE Clause Evaluation
  4. Conditions and Expressions
  5. Membership Conditions and Range Conditions
  6. Matching Conditions
  7. Regular Expressions and Handling NULL
  8. Placement of Join Conditions
By: O'Reilly Media
Rating: starstarstarstarstar / 22
October 19, 2004

print this article



Along with determining whether two expressions are identical, it is often useful to determine whether one expression can be found within a set of expressions. Using the IN operator, you can build conditions that will evaluate to TRUE if a given expression exists in a set of expressions:

s.name IN ('Acme Industries', 'Tilton Enterprises')

You may also use the NOT IN operator to determine whether an expression does not exist in a set of expressions:

s.name NOT IN ('Acme Industries', 'Tilton Enterprises')

Most people prefer to use a single condition with IN or NOT IN instead of writing multiple conditions using = or !=, so, with that in mind, here’s one last stab at the Acme/Tilton query:

SELECT p.part_nbr, p.name, p.supplier_id, p.status, p.inventory_qty,
  s.supplier_id, s.name
FROM part p, supplier s
WHERE s.supplier_id = p.supplier_id
  AND s.name NOT IN ('Acme Industries', 'Tilton Enterprises');

Along with prefabricated sets of expressions, subqueries may be employed to generate sets on the fly. If a subquery returns exactly one row, you may use a comparison operator; if a subquery returns more than one row, or if you’re not sure whether the subquery might return more than one row, use the IN operator. The following example updates all orders that contain parts supplied by Eastern Importers:

UPDATE cust_order
SET sale_price = sale_price * 1.1
WHERE cancelled_dt IS NULL
  AND ship_dt IS NULL
  AND order_nbr IN
  (SELECT li.order_nbr
    FROM line_item li, part p, supplier s
    WHERE s.name = 'Eastern Importers'
      AND s.supplier_id = p.supplier_id
      AND p.part_nbr = li.part_nbr);

The subquery evaluates to a (potentially empty) set of order numbers. All orders whose order number exists in that set are then modified by the UPDATE statement.

Range Conditions

If you are dealing with dates or numeric data, you may be interested in whether a value falls within a specified range rather than whether it matches a specific value or exists in a finite set. For such cases, you may use the BETWEEN operator, as in:

DELETE FROM cust_order
WHERE order_dt BETWEEN '01-JUL-2001' AND '31-JUL-2001';

To determine whether a value lies outside a specific range, you can use the NOT BETWEEN operator:

SELECT order_nbr, cust_nbr, sale_price
FROM cust_order
WHERE sale_price NOT BETWEEN 1000 AND 10000;

When using BETWEEN, make sure the first value is the lesser of the two values provided. While “BETWEEN 01-JUL-2001 AND 31-JUL-2001” and “BETWEEN 31-JUL-2001 AND 01-JUL-2001” might seem logically equivalent, specifying the higher value first guarantees that your condition will always evaluate to FALSE. Keep in mind that X BETWEEN Y AND Z is evaluated as X >= Y AND X <= Z.

Ranges may also be specified using the operators <, >, <=, and >=, although doing so requires writing two conditions rather than one. The previous query can also be expressed as:

SELECT order_nbr, cust_nbr, sale_price
FROM cust_order
WHERE sale_price < 1000 OR sale_price > 10000;

Buy the book!If you've enjoyed what you've seen here, or to get more information, click on the "Buy the book!" graphic. Pick up a copy today!

Visit the O'Reilly Network http://www.oreillynet.com for more online content.

>>> More Oracle Articles          >>> More By O'Reilly Media

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: