2.2 WHERE to the Rescue

Hopefully, the scenarios in the previous section give you some insight into the utility of the WHERE clause, including the ability to:

Filter out unwanted data from a query's result set.
Isolate one or more rows of a table for modification.
Conditionally join two or more data sets together.

To see how these things are accomplished, let's add a WHERE clause to the previous SELECT statement, which strives to locate all parts supplied by Acme Industries. Here's the query with the new WHERE clause:

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 = 'Acme Industries';

The WHERE clause here is comprised of two parts, known as conditions, which are evaluated separately. Conditions always evaluate to either TRUE or FALSE; if there are multiple conditions in a WHERE clause, they all must evaluate to TRUE for a given row to be included in the result set. Actually, that's a bit of an oversimplification. As you will see later, using the OR and NOT operators allows the WHERE clause to evaluate to TRUE even if individual conditions evaluate to FALSE.

For this example, a row created by combining data from the part and supplier tables will only be included in the final result set if both tables share a common value for the supplier_id column, and if the value of the name column in the supplier table matches 'Acme Industries'. Any other permutation of data from the two tables would evaluate to FALSE and be discarded.

For this chapter only, we'll use the older style of join syntax in which you specify join conditions in the WHERE clause. We do this to explore the full functionality of the WHERE clause.

With the addition of the WHERE clause to the previous example, therefore, Oracle will take on the work of discarding undesired rows from the result set, and only 50 rows would be returned by the query, rather than 1,000,000. Now that you have retrieved the 50 rows of interest from the database, you can begin the process of modifying the data. Keep in mind, however, that with the WHERE clause at your disposal you will no longer need to delete and re-insert your modified data; instead, you can use the UPDATE statement to modify specific rows based on the part_nbr column, which is the unique identifier for the table:

UPDATE part

SET status = 'DISCONTINUED'

WHERE part_nbr = 'AI5-4557';

While this is certainly an improvement, you can do even better. If your intent is to modify the status for all 50 parts supplied by Acme Industries, there is no need to execute a separate query at all. Simply execute a single UPDATE statement that finds and modifies all 50 records:

UPDATE part

SET status = 'DISCONTINUED'

WHERE supplier_id = 

 (SELECT supplier_id 

  FROM supplier

  WHERE name = 'Acme Industries');

The WHERE clause in this statement consists of a single condition that equates the supplier_id column to the value returned by the subquery against the supplier table. Subqueries are covered extensively in Chapter 5, so don't worry if this looks a bit intimidating. The net result is that the condition will be rewritten to use the value returned by the subquery, as in:

UPDATE part

SET status = 'DISCONTINUED'

WHERE supplier_id = 1;

When executed, the condition evaluates to TRUE for exactly 50 of the 10,000 rows in the part table, and the status of those 50 rows changes to DISCONTINUED.