Whether you are querying, modifying, or deleting data, the WHERE clause is the mechanism for identifying what data you want to work with. This chapter explores the role of the WHERE clause in SQL statements, as well as the various options available when building a WHERE clause.
Before delving into the
let’s imagine life without it. Say that you are
interested in doing some maintenance on the data in the
part table. To inspect the data in the table, you
issue the following query:
SELECT part_nbr, name, supplier_id, status, inventory_qty FROM part;
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
Once you have made the required modifications to your data in memory,
it is time to apply the changes to the
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 (the ability to perform predictably as load increases).