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

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

Practice #1: Select all products from a category.

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

Other tutorials in this category

1. The Basic SELECT Statement

2. Using Column Alias in SELECT Statement

3. Using Literal Character Strings

4. Perform Arithmetic Operations

5. Sorting Data

6. Using LIMIT Clause to Constrain the Number of Rows Retrieved

7. Using Comparison Operators, Part I

8. Using Comparison Operators, Part II

9. Using LIKE Comparison Operator

10. Using Logical Operators

11. Eliminating Duplicate Rows

Back to Tutorial Index Page

	Custom Search