Mastering Oracle SQL

WHERE to the Rescue

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

1. Filter out unwanted data from a query’s result set.

2. Isolate one or more rows of a table for modification.

3. 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:

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.^[5] 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’.^[6] Any other permutation of data from the two tables would evaluate to FALSE and be discarded.

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