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;
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.