CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Where Clause
Version 11.1
 
Oracle WHERE Clause Examples
The WHERE Clause serves two different purposes. One is to limit the records affected by a DML statement (SELECTs,UPDATEs, or DELETEs). The other is to join two or more tables, views, or materialized views for DML.

The WHERE clause is the specifier for identifying what data you want to work with when querying, updating, or deleting data. This page briefly explains the use of the WHERE clause in SQL statements, including the available options available when using the WHERE clause. FOr more detailed examples, see the links below.

Before examining the WHERE clause, it's important to understand what happens if you use queries without it. For example, take the case where you want to do some work on the data in the 'items' table. To look at the data in the 'items' table you could use this query:

SELECT part_number, name, source_id, item_status, inventory_quant
FROM items;

If the items table contains 5000 items, you'll get back all 5000 rows, each with 5 columns. All 5000 rows would be loaded into memory where your changes would be made. When the changes have been made on the data in memory, then the changes would be made to the items table. Without being able to identify only the rows to be changed, you'll have to delete all rows in the table and then re-insert all 5000 rows:

DELETE FROM items;
INSERT INTO items (part_number, name, source_id, item_status, inventory_quant) VALUES ('123-AX55', 'Door Handle', 1, 'AVAILABLE', 1);

... repeat 4999 more inserts similar to the one above ...

This kind of operation has negative effects on overall performance, concurrency (the ability for multiple users to update data at the same time), and scalability (the ability of the database to perform acceptably as the load on the database increases).

But what if there was a way you could modify only the data in the 'items' table supplied by XYZ Corp? Since the providers's name is stored in the 'providers' table, all you'll need to do is include both the 'items' and 'providers' tables in the FROM clause:

SELECT p.items_number, i.name, i.source_id, i.item_status, i.inventory_quant, s.source_id, s.name
FROM items i, providers s;

If 100 companies supply the 5000 items in the 'items' table, the query above will return 500,000 rows. Known as a 'Cartesian join', this number is the result of every possible combination of all rows from the two tables. Cartesian joins like this usually return useless (unnecessary) combinations of rows and are rarely used.

As the 500,000 rows are processed you would retain only those rows where the values of p.source_id and s.source_id are identical and where the s.name column matches 'XYZ Corp'. If XYZ Corp. supplies only 50 of the 5000 items in your database, you will end up discarding 499,950 of the 500,000 rows returned by your query. To avoid these kinds of problems, the WHERE clause is used to:

  • To filter or screen out unwanted data from a query's result set.
  • To specify one or more rows of a given table for modification.
  • To conditionally join two or more data sets together.

The way this is done is to add a WHERE clause to the SELECT statement, which will locate all parts supplied by XYZ Corp. This is an example query using the new WHERE clause:

SELECT i.item_number, i.name, i.supplier_id, i.status, i.inventory_quant,
s.provider_id, s.name
FROM items i, provider s
WHERE s.provider_id = i.provider_id
AND s.name = 'XYZ Corp';

In the example above the WHERE clause contains two parts which are evaluated separately. The parts of the WHERE clause are referred to as 'conditions'. Conditions always evaluate to either TRUE or FALSE. If there are multiple conditions in a WHERE clause, they must all evaluate to the TRUE condition for a row to be returned or included in the result set.

In the query above, a row created by combining data from the 'items' and 'provider' tables will only be returned in the result set if both tables share a common value for the provider_id column, AND if the value of the name column in the provider table matches 'XYZ Corp.'.

The following links all have demonstrations of using the WHERE clause, each in a different context.

Additional information is also available in the Oracle Definitions Reference: The WHERE Clause.


 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [66 users online]    © 2010 psoug.org