Combining WHERE Clauses

The examples you have seen so far perform filtering on a query based on only a single condition. To provide greater control over a result set, MySQL enables you to combine a number of conditions in a WHERE clause. They are joined using the logical operator keywords AND and OR.

Using the AND Operator

After adding a WHERE clause to filter a query, you can filter the results further by adding another condition with the AND operator.

This is commonly used to restrict the query results based on the values of two or more columns, as shown in the following example:

 mysql> SELECT * From orders     -> WHERE customer_code = 'Scicorp'     -> AND order_date >= '2006-02-01'; +----+---------------+------------+ | id | customer_code | order_date | +----+---------------+------------+ |  4 | Scicorp       | 2006-02-02 | |  5 | SCICORP       | 2006-02-05 | +----+---------------+------------+ 2 rows in set (0.00 sec) 

This query returns only rows from the orders table in which both conditions hold true. The customer_code must be SCICORP, and the order_date must be on or after February 1, 2006.

AND

For a query row to be returned, the table data in question must satisfy all the conditions separated by an And operator. If any one condition fails for a record, that record is filtered out.

Using the OR Operator

Whereas the AND operator specifies a filter that further restricts the number of rows returned by a query, the OR operator is used to relax the filtering criteria by specifying alternative filter conditions. If one or more of the conditions separated by an OR operator hold true for a row in the table, that record will appear in the query results.

The following example shows the same example you saw for the AND operator, but using an OR instead:

 mysql> SELECT * FROM orders     -> WHERE customer_code = 'Scicorp'     -> OR order_date >= '2006-02-01'; +----+---------------+------------+ | id | customer_code | order_date | +----+---------------+------------+ |  3 | SCICORP       | 2006-01-23 | |  4 | SCICORP       | 2006-02-02 | |  5 | SCICORP       | 2006-02-05 | |  6 | MUSGRP        | 2006-02-01 | |  7 | MUSGRP        | 2006-02-02 | +----+---------------+------------+ 5 rows in set (0.00 sec) 

The result set for this query includes all orders for SCICORP, regardless of order_date, as well as any orders for other customers that were placed on or after February 1.

OR

For a query row to be returned, the table data in question must satisfy at least one of the conditions separated by an OR operator. Every condition must fail for a record to be filtered out.

This example finds all the customers who have the name Franklin, whether that is their first name or last name:

 mysql> SELECT first_name, last_name     -> FROM customer_contacts     -> WHERE first_name = 'Franklin'     -> OR last_name = 'Franklin'; +------------+-----------+ | first_name | last_name | +------------+-----------+ | Franklin   | Roosevelt | | Benjamin   | Franklin  | +------------+-----------+ 2 rows in set (0.00 sec)  

Filtering on Multiple Values

You could use the OR operator with equals conditions to specify multiple filter values for the same column. For instance, the following query finds all the customer contacts called either Benjamin or Charles:

 mysql> SELECT first_name, last_name     -> FROM customer_contacts     -> WHERE first_name = 'Benjamin'     -> OR first_name = 'Charles'; +------------+-----------+ | first_name | last_name | +------------+-----------+ | Charles    | Darwin    | | Benjamin   | Franklin  | | Benjamin   | Britten   | +------------+-----------+ 3 rows in set (0.00 sec) 

However, because this is a relatively common type of filter, there is a handy shortcut. You can use the IN operator to perform exactly this type of filter in a single condition. IN works like multiple equals operators and takes a comma-separated list of values, enclosed in parentheses.

Brackets

The proper name for the brackets used in MySQL is parentheses. Because MySQL uses only parentheses, however, you can refer to them as brackets without confusing them with square brackets or braces.

The following query is equivalent to the previous example:

 mysql> SELECT first_name, last_name     -> FROM customer_contacts     -> WHERE first_name In ('Benjamin', 'Charles'); +------------+-----------+ | first_name | last_name | +------------+-----------+ | Charles    | Darwin    | | Benjamin   | Franklin  | | Benjamin   | Britten   | +------------+-----------+ 3 rows in set (0.00 sec) 

You can already see that this query is more concise and easier to read when you use IN.Imagine how cumbersome a query would become without it if you had to use the OR operator with a long list of values to compare.

Instead of matching against a list of values, the BETWEEN operator enables you to search for data within a given range. It takes two target values separated by the keyword AND. Rows in which the column data is betweenand includingthose values are returned. The following example finds only orders placedin a given week in January:

 mysql> SELECT *     -> FROM orders     -> WHERE order_date Between '2006-01-24' AND '2006-01-30'; +----+---------------+------------+ | id | customer_code | order_date | +----+---------------+------------+ |  2 | PRESINC       | 2006-01-26 | |  3 | SCICORP       | 2006-01-23 | +----+---------------+------------+ 2 rows in set (0.00 sec) 

Date Comparisons

The previous example used the BETWEEN operator to compare two dates. In MySQL, dates are compared as numeric values, with earlier dates having lower values than later dates. You will learn more about date values in MySQL in Lesson 8, "Date Operators and Functions."

Negating a Condition

Using the NOT keyword negates a conditionit makes it behave in the opposite way.

A powerful use of NOT is the NOT IN condition. Instead of specifying a list of values that a column value should match, you specify a list of values that should be excluded from the query result. The following query finds customers from a given company code but also excludes a list of names from the result:

 mysql> SELECT first_name, last_name FROM customer_contacts     -> WHERE customer_code = 'PRESINC'     -> AND first_name NOT IN ('Abraham', 'Theodore'); +------------+-----------+ | first_name | last_name | +------------+-----------+ | Richard    | Nixon     | | Franklin   | Roosevelt | +------------+-----------+ 2 rows in set (0.00 sec) 

Similarly, you can use the NOT operator with a BETWEEN statement to exclude a range of values from the query result. The following example finds only orders that were placed outside the period given:

 mysql> SELECT * FROM orders     -> WHERE order_date NOT BETWEEN '2006-01-26' AND '2006-02-03'; +----+---------------+------------+ | id | customer_code | order_date | +----+---------------+------------+  |  1 | PRESINC       | 2006-01-23 | |  3 | SCICORP       | 2006-01-23 | |  5 | SCICORP       | 2006-02-05 | +----+---------------+------------+ 3 rows in set (0.00 sec)  

Operator Precedence

Consider the following query, which contains both an AND and an OR operator:

 mysql> SELECT customer_code, first_name, last_name     -> FROM customer_contacts     -> WHERE customer_code = 'SCICORP'     -> AND first_name = 'Albert'     -> OR first_name = 'Benjamin'; 

Before looking at the output from this query, try to determine what MySQL is actually looking for here. This query could filter the customer contact data in two possible ways.

You might expect that the query would return contacts for the company SCICORP with one of the first names given. Or you might think that it will return anyone named Albert from SCICORP, as well as any other contact named Benjamin. However, unless you know whether MySQL assigns more importance to an AND or an OR, you will not be able to say for sure without running the query.

The actual results from the previous query are shown here:

 +---------------+------------+-----------+ | customer_code | first_name | last_name | +---------------+------------+-----------+ | SCICORP       | Albert     | Einstein  | | SCICORP       | Benjamin   | Franklin  | | MUSGRP        | Benjamin   | Britten   | +---------------+------------+-----------+ 3 rows in set (0.00 sec) 

As you can see, a Benjamin from a company other than SCICORP appears in the results. This is because MySQL treats an AND with higher precedence than an OR.The effect is that the conditions on either side of the AND are evaluated first, before the OR operator is considered.

Precedence

The order in which components of SQL statements are evaluated is called precedence. For conditional operators, AND has a higher precedence than OR, which, in turn, has a higher precedence than NOT.

To override the natural order of evaluation, you can put parentheses around conditions to indicate that they should be evaluated together. The following query uses parentheses to override the operator precedence of the previous example:

 mysql> SELECT customer_code, first_name, last_name     -> FROM customer_contacts     -> WHERE customer_code = 'SCICORP'     -> AND (first_name = 'Albert'     -> OR first_name = 'Benjamin'); +---------------+------------+-----------+ | customer_code | first_name | last_name | +---------------+------------+-----------+ | SCICORP       | Albert     | Einstein  | | SCICORP       | Benjamin   | Franklin  | +---------------+------------+-----------+ 2 rows in set (0.00 sec) 

This time, the two queries on either side of the OR operator are evaluated first, before the AND operator is considered. This query returns a row only if the customer_code condition is true and at least one of the conditions in the OR is met.

Using Parentheses

You can use parentheses to indicate the order of evaluation even if doing so will not affect thenatural operator precedence. This will make yourqueries more readable.