Home arrow Oracle arrow Page 2 - Mastering the WHERE Clause

WHERE to the Rescue - 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

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.

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

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: