Role of the WHERE Clause in SQL DML Requests
SELECT, DELETE, and UPDATE requests can all specify a WHERE clause. The WHERE clause filters row selection, limiting access to only those rows or row ranges specified in the clause. It is equivalent to the RESTRICT (or SELECT) operator of the relational algebra.
The MERGE statement uses an ON clause predicate to filter row selection.
Using a carefully constructed WHERE or ON clause to limit the number of rows processed by a DML statement enhances throughput markedly. If no WHERE clause is specified, then all rows participate in the selection, deletion, or update activities specified by a particular DML statement.
Note: The WHERE CURRENT OF clause available for DELETE and UPDATE statements in embedded SQL and stored procedures acts on preselected spool rows, so it does not do I/O for each individual row in the cursor.