Use a WHERE Clause to Filter Rows

Many novices retrieve all the rows from a table when they only want one row (or a few rows). This is very wasteful . A better approach is to use a WHERE clause in a SELECT statement. That way, you restrict the rows retrieved to just those actually needed.

For example, lets say you want the details for customer #1 and #2. The following query retrieves all the rows from the customers table in the store schema (wasteful):

 -- BAD (retrieves all rows from the customers table)  SELECT *   FROM customers;  CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE ----------- ---------- ---------- --------- ------------  1 John Brown 01-JAN-65 800-555-1211  2 Cynthia Green 05-FEB-68 800-555-1212  3 Steve White 16-MAR-71 800-555-1213  4 Gail Black 800-555-1214  5 Doreen Blue 20-MAY-70 

The next query adds a WHERE clause to the previous example to limit the rows to just those whose customer_id is 1 or 2:

 -- GOOD (uses a WHERE clause to limit rows retrieved)  SELECT *   FROM customers   WHERE customer_id IN (1, 2);  CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE ----------- ---------- ---------- --------- ------------  1 John Brown 01-JAN-65 800-555-1211  2 Cynthia Green 05-FEB-68 800-555-1212 

You should avoid using functions in the WHERE clause as that increases execution time.