13.1 What Is the WHERE Clause?
-
Understand and be confident writing and running SQL with a WHERE clause.
-
Know how to use comparison operators in WHERE clauses.
-
Know how to work with NULL values in SQL queries.
-
Understand and be confident writing and running compound WHERE clauses.
-
Know how to work with date fields in a WHERE clause.
In Lesson 12, we discussed the LIMIT clause that reduces the number of rows returned in a query result. When a LIMIT clause is used, the DBMS returns the specified number of rows starting with the first row in a query result. In this lesson, we will discuss a much more powerful and focused way to reduce the number of rows returned in a query result. This approach uses the WHERE clause of the SQL statement.
The WHERE clause in a SQL statement is used to restrict the results of a query to only those rows that meet specified criteria. These criteria are applied to the values of the fields. For example, we may wish to view only those members who have the position of “President.” There is only one member who meets this criterion, Sacha Harrison (see Figure 13.1). All other members will be excluded from the query result because they do not meet this criterion.
The WHERE clause appears in the SQL statement after the FROM clause. The general syntax of a WHERE clause is the keyword WHERE followed by the field to which we wish to apply the criterion, a mathematical comparison operator, and the value restriction we wish to add to that field. The simplest form of this syntax is WHERE fieldname = value. The SQL statement used for our example in Figure 13.1 is below. Notice the syntax of the WHERE clause. Here we are restricting the query results to those members whose position equals "President".
SELECT first_name, last_name, position
FROM member
WHERE position = 'President'
Most WHERE clause conditions will follow the pattern in the example: field, comparison operator, value. There are cases when we need to restrict our query results by more than one criterion. In other cases, we may not know the exact values that we want to use in the restriction.
In this lesson, we will first discuss the comparison operators. We will then cover how to include multiple criteria in our WHERE clauses. Finally, we will outline how to use criteria that incorporate wildcard characters to provide flexibility in the values used to restrict query results.