2.2 WHERE to the Rescue

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

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:

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 in order for a given row to be included in the result set.^[1] 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 tables matches 'Acme Industries'.^[2] Any other permutation of data from the two tables would evaluate to FALSE and be discarded.

^[1] This is 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.

^[2] Another oversimplification. The Oracle optimizer (the component tasked with finding the most efficient way to execute a query) doesn't first create every possible combination of rows from every table or view in the FROM clause before it begins evaluating conditions. Rather, the optimizer chooses the order in which to evaluate conditions and join data sets so execution time is (hopefully) minimized.

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 will 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, we 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 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 a query against the supplier table. A query wrapped in parentheses inside another SQL statement is known as a subquery; subqueries will be studied 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.