Skip to Main Content
Mastering Oracle SQL
book

Mastering Oracle SQL

by Sanjay Mishra, Alan Beaulieu
April 2002
Intermediate to advanced content levelIntermediate to advanced
336 pages
9h 58m
English
O'Reilly Media, Inc.
Content preview from Mastering Oracle SQL

Chapter 2. The WHERE Clause

Whether we are querying, modifying, or deleting data, the WHERE clause is the mechanism for identifying the sets of data we want to work with. In this chapter, we explore the role of the WHERE clause in SQL statements, as well as the various options available when building a WHERE clause.

Life Without WHERE

Before we delve into the WHERE clause, let’s imagine life without it. Say that you are interested in doing some maintenance on the data in the part table. In order to inspect the data in the table, you issue the following query:

SELECT part_nbr, name, supplier_id, status, inventory_qty
FROM part;

If the part table contains 10,000 items, the result set returned by the query would consist of 10,000 rows, each with 5 columns. You would then load the 10,000 rows into memory and make your modifications.

Once you have made the required modifications to your data in memory, it is time to apply the changes to the part table. Without the ability to specify the rows to modify, you have no choice but to delete all rows in the table and re-insert all 10,000 rows:

DELETE FROM part;

INSERT INTO part (part_nbr, name, supplier_id, status, inventory_qty)
VALUES ('XY5-1002', 'Wonder Widget', 1, 'IN-STOCK', 1);

/* 9,999 more INSERTs on the wall, 9,999 more INSERTS... */

While this approach works in theory, it wreaks havoc on performance, concurrency (the ability for more than one user to modify data simultaneously), and scalability.

Now imagine that you want to modify ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle SQL

Oracle SQL

Dan Hotka
Oracle SQL Developer

Oracle SQL Developer

Ajith Narayanan, Susan Harper

Publisher Resources

ISBN: 0596001290Supplemental ContentCatalog PageErrata