Use WHERE Clause to Conditionally Select Rows in SQL SELECT Statement for MySQL

Use WHERE Clause to Conditionally Select Rows

The WHERE clause indicates the condition or conditions that rows must satisfy to be selected. The WHERE condition is an expression that evaluates to true for each row to be selected. The statement selects all rows if there is no WHERE clause.

Read Prerequisites for this tutorial and practices if you haven't done so.

Consider the following facts when using WHERE clause in a SELECT statement:

In a SELECT statement, the WHERE clause directly follows the FROM clause.
In the WHERE clause, the condition(s) is made up of any of column names, literal values, expressions (such as arithmetic expressions), constants, list of values, operators, or MySQL built-in or user-defined functions (except for aggregate functions).

Practice #1: Select all products from a category.

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

-- Retrieve all products of the
-- beverages category (CategoryID = 1).
SELECT ProductID, ProductName
FROM products
WHERE CategoryID = 1;

Query result set - 12 rows returned:
Select all products from beverages category

Practice #2: Use arithmetic operation in WHERE clause

/*
Use arithmetic operation in WHERE clause.
 
In this query, we want to retrieve order line items
where total price ordered is greater than 10,000
*/
SELECT OrderID, ProductID, Quantity, UnitPrice, UnitPrice*Quantity
FROM order_details
WHERE UnitPrice*Quantity > 10000;

Note that, in real world applications, we don't use UnitPrice*Quantity in WHERE clause like this as it forces database engine to perform row-by-row scan of the whole table to do the calculation. Table scan is a very slow operation if the table is very large. To speed up such query, create a new column to store total price and create an index on this new column. This way, the new column can be used in WHERE clause and index can be used rather than table scan.

Query result set - 6 rows returned:
Use arithmetic operation in WHERE clause

Next tutorial: Sort Data by ORDER BY Clause
Back to Section Index: Single Table SELECT Statement
Back to Tutorial Index: MySQL Database How-to Articles and Tutorials

	Custom Search