Home arrow Oracle arrow Page 3 - Mastering the WHERE Clause

WHERE Clause Evaluation - 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.)

TABLE OF CONTENTS:
  1. Mastering the WHERE Clause
  2. WHERE to the Rescue
  3. WHERE Clause Evaluation
  4. Conditions and Expressions
  5. Membership Conditions and Range Conditions
  6. Matching Conditions
  7. Regular Expressions and Handling NULL
  8. Placement of Join Conditions
By: O'Reilly Media
Rating: starstarstarstarstar / 22
October 19, 2004

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.

Intermediate result Final result
WHERE TRUE OR TRUE TRUE
WHERE FALSE OR FALSE FALSE
WHERE FALSE OR TRUE TRUE
WHERE TRUE OR FALSE TRUE

Table 2-2. Multiple-condition evaluation using OR

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.

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

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

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.

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

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

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.  

Buy the book!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!

Visit the O'Reilly Network http://www.oreillynet.com for more online content.



 
 
>>> More Oracle Articles          >>> More By O'Reilly Media
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: