Membership Conditions and Range Conditions - Oracle

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.

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;

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.