MySQL —

Filter Data 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 = 8.99;

p>

This statement retrieves two columns from the products table, but instead of returning all rows, only rows with a prod_price value of 8.99 are returned, as follows:

+-----------+------------+
| prod_name | prod_price |
+-----------+------------+
| product 3 |       8.99 |
| product 6 |       8.99 |
+-----------+------------+

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 enables you to do more than just test for equality.

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.

SQL VS 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.