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