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 table'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 = 3.49;
This statement retrieves two columns from the products table, but instead of returning all rows, only rows with a prod_price value of 3.49 are returned, as follows:
prod_name prod_price ------------------- ---------- Fish bean bag toy 3.4900 Bird bean bag toy 3.4900 Rabbit bean bag toy 3.4900
This example uses a simple equality test: it checks to see if a column has a specified value, and it filters the data accordingly. But SQL lets you do more than just test for equality.
Tip
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 databases job will dramatically impact application performance and will create 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 usage.
Caution
WHERE Clause Position When using both ORDER BY and WHERE clauses, make sure that ORDER BY comes after the WHERE, otherwise an error will be generated. (See Lesson 3, "Sorting Retrieved Data," for more information on using ORDER BY .)