2.3 WHERE Clause Evaluation

Now that you have seen the WHERE clause in action, let's take a look at how it is evaluated. As previously mentioned, the WHERE clause consists of one or more conditions that evaluate independently to TRUE or FALSE. If your WHERE clause consists of multiple conditions, the conditions are separated by the logical operators AND and OR. Depending on the outcome of the individual conditions and the placement of these logical operators, Oracle will assign a final value of TRUE or FALSE to each candidate row, thereby determining whether a row will be included in the final result set.

Here's another look at the Acme Industries 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 = 'Acme Industries';

The WHERE clause consists of two conditions separated by AND. Thus, a row will only be included if both conditions evaluate to TRUE. Table 2-1 shows the possible scenarios when conditions are replaced by their possible outcomes.

Table 2-1. Multiple-condition evaluation using AND

Intermediate result

Final result

WHERE TRUE AND TRUE

TRUE

WHERE FALSE AND FALSE

FALSE

WHERE FALSE AND TRUE

FALSE

WHERE TRUE AND FALSE

FALSE


Using basic logic rules, you can see that the only combination of outcomes that results in a final value of TRUE being assigned to a candidate row is where both conditions evaluate to TRUE. Table 2-2 demonstrates the possible outcomes if the conditions had been separated by OR rather than AND.

Table 2-2. Multiple-condition evaluation using OR

Intermediate result

Final result

WHERE TRUE OR TRUE

TRUE

WHERE FALSE OR FALSE

FALSE

WHERE FALSE OR TRUE

TRUE

WHERE TRUE OR FALSE

TRUE


Next, let's spice the query up a bit by including parts supplied by either Acme Industries or Tilton Enterprises:

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'

    OR s.name = 'Tilton Enterprises');

There are now three separate conditions separated by AND and OR with parentheses surrounding two of the conditions. Table 2-3 illustrates the possible outcomes.

Table 2-3. Multiple-condition evaluation using AND and OR

Intermediate result

Final result

WHERE TRUE AND (TRUE OR FALSE)

TRUE

WHERE TRUE AND (FALSE OR TRUE)

TRUE

WHERE TRUE AND (FALSE OR FALSE)

FALSE

WHERE FALSE AND (TRUE OR FALSE)

FALSE

WHERE FALSE AND (FALSE OR TRUE)

FALSE

WHERE FALSE AND (FALSE OR FALSE)

FALSE


Since a particular part cannot be supplied by both Acme Industries and Tilton Enterprises, the intermediate results TRUE AND (TRUE AND TRUE) and FALSE AND (TRUE AND TRUE) were not included in Table 2-3.

To liven things up even more, here's an example using the NOT operator. The following query returns data for parts supplied by anyone other than Acme Industries or Tilton Enterprises:

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 NOT (s.name = 'Acme Industries'

    OR s.name = 'Tilton Enterprises');

Table 2-4 demonstrates how the addition of the NOT operator changes the outcome.

Table 2-4. Multiple-condition evaluation using AND, OR, and NOT

Intermediate result

Final result

WHERE TRUE AND NOT (TRUE OR FALSE)

FALSE

WHERE TRUE AND NOT (FALSE OR TRUE)

FALSE

WHERE TRUE AND NOT (FALSE OR FALSE)

TRUE

WHERE FALSE AND NOT (TRUE OR FALSE)

FALSE

WHERE FALSE AND NOT (FALSE OR TRUE)

FALSE

WHERE FALSE AND NOT (FALSE OR FALSE)

FALSE


The use of the NOT operator in the previous example is a bit forced; later examples will demonstrate more natural ways of expressing the same logic.