Using the WHERE Clause
Database tables usually contain large amounts of data, and you seldom need to retrieve all the rows in a table. More often than not, you'll want to extract a subset of the t able's data as needed for specific operations or reports. Retrieving just the data you want involves specifying search criteria, also known as a filter condition.
Within a SELECT statement, data is filtered by specifying search criteria in the WHERE clause. The WHERE clause is specified right after the table name (the FROM clause) as follows:
SELECT prod_name, prod_price FROM products WHERE prod_price = 2.50;
This statement retrieves two columns from the products table, but instead of returning all rows, only rows with a prod_price value of 2.50 are returned, as follows:
+---------------+------------+ | prod_name | prod_price | +---------------+------------+ | Carrots | 2.50 | | TNT (1 stick) | 2.50 | +---------------+------------+
SQL Versus Application Filtering Data can also be filtered at the application level. To do this, the SQL SELECT statement retrieves more data than is actually required for the client application, and the client code loops through the returned data to extract just the needed rows.
As a rule, this practice is strongly discouraged. Databases are optimized to perform filtering quickly and efficiently. Making the client application (or development language) do the database's job dramatically impacts application performance and creates applications that cannot scale properly. In addition, if data is filtered at the client, the server has to send unneeded data across the network connections, resulting in a waste of network bandwidth resources.
WHERE Clause Position When using both ORDER BY and WHERE clauses, make sure ORDER BY comes after the WHERE; otherwise an error will be generated. (See Chapter 5, "Sorting Retrieved Data," for more information on using ORDER BY.)