Basic SQL - Learning MySQL - page 146

Combining conditions with AND, OR, NOT, and XOR
So far, we've used the WHERE clause to test one condition, returning all rows that meet
it. You can combine two or more conditions using the Boolean operators AND , OR , NOT ,
and XOR .
Let's start with an example. Suppose you want to find all albums with a title that begins
with a character greater than C but less than M . This is straightforward with the AND
operator:
mysql> SELECT album_name FROM album WHERE
-> album_name > "C" AND album_name < "M";
+-----------------------+
| album_name |
+-----------------------+
| Let Love In |
| Live Around The World |
| In A Silent Way |
| Exile On Main Street |
| Light Years |
+-----------------------+
5 rows in set (0.06 sec)
The AND operation in the WHERE clause restricts the results to those rows that meet both
conditions.
The OR operator is used to find rows that meet at least one of several conditions. To
illustrate, imagine you want a list of all albums that have a title beginning with L , S , or
P . You can do this with two OR and three LIKE clauses:
mysql> SELECT album_name FROM album WHERE
-> album_name LIKE "L%" OR
-> album_name LIKE "S%" OR
-> album_name LIKE "P%";
+--------------------------+
| album_name |
+--------------------------+
| Let Love In |
| Substance (Disc 2) |
| Live Around The World |
| Power, Corruption & Lies |
| Substance 1987 (Disc 1) |
| Second Coming |
| Light Years |
+--------------------------+
7 rows in set (0.00 sec)
The OR operations in the WHERE clause restrict the answers to those that meet any of the
three conditions. As an aside, it's particularly obvious in this example that the results
are reported without sorting; in this case, they're reported in the order they were added
to the database. We'll return to sorting output later in “ORDER BY Clauses.”