This chapter is from the book
This chapter is from the book
This chapter is from the book
Using WHERE in Your Queries
You have learned numerous ways to retrieve particular columns from your tables, but not specific rows. This is when the WHERE clause comes into play. From the basic SELECT syntax, you see that WHERE is used to specify a particular condition:
SELECT expressions_and_columns FROM table_name [WHERE some_condition_is_true]
An example would be to retrieve all the records for people with the last name of "Smith":
mysql> SELECT * FROM master_name WHERE lastname = 'Smith'; +---------+---------------------+---------------------+-----------+----------+ | name_id | name_dateadded | name_datemodified | firstname | lastname | +---------+---------------------+---------------------+-----------+----------+ | 1 | 2001-10-29 13:11:00 | 2001-10-29 13:11:00 | John | Smith | | 2 | 2001-10-29 13:11:00 | 2001-10-29 13:11:00 | Jane | Smith | | 4 | 2001-10-29 13:11:00 | 2001-10-29 13:11:00 | Andy | Smith | | 43 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | Albert | Smith | +---------+---------------------+---------------------+-----------+----------+ 4 rows in set (0.00 sec)
Just like when you add records, if you use strings or dates in your WHERE clauses, you must surround them with quotation marks. Using single quotes or double quotes makes no difference, but the same rules for escaping characters apply here as well. For example, if you insert a record into the master_name table for Patrick O'Brien using single quotes:
mysql> INSERT INTO master_name VALUES ('', NOW(), NOW(), 'Patrick', 'O\'Brien');
When you select the record using WHERE, you have to escape the single quote character:
mysql> SELECT * FROM master_name WHERE lastname = 'O\'Brien'; +---------+---------------------+---------------------+-----------+----------+ | name_id | name_dateadded | name_datemodified | firstname | lastname | +---------+---------------------+---------------------+-----------+----------+ | 46 | 2001-10-31 08:32:53 | 2001-10-31 08:32:53 | Patrick | O'Brien | +---------+---------------------+---------------------+-----------+----------+ 1 row in set (0.00 sec)
If you use an integer as part of your WHERE clause, quotation marks are not required:
mysql> SELECT firstname, lastname FROM master_name WHERE name_id = 1; +-----------+----------+ | firstname | lastname | +-----------+----------+ | John | Smith | +-----------+----------+ 1 row in set (0.00 sec)
Using Operators in WHERE Clauses
You've been using the equal sign (=) in your WHERE clauses to determine the truth of a condition—is one thing equal to another. There are many types of operators you can use, with comparison operators and logical operators being the most popular types.
Comparison operators should look familiar to you if you think about the first day of Algebra class.
Table 10.1 Basic Comparison Operators and Their Meanings
|
Operator |
Meaning |
|
= |
Equal to |
|
!= |
Not equal to |
|
<= |
Less than or equal to |
|
< |
Less than |
|
>= |
Greater than or equal to |
|
> |
Greater than |
When you use comparison operators on strings, the comparison is case insensitive. Look at the next two queries and their results as an example. If the comparison were case sensitive, the first query would return no results.
mysql> SELECT firstname, lastname FROM master_name WHERE lastname = 'SMITH'; +-----------+----------+ | firstname | lastname | +-----------+----------+ | John | Smith | | Jane | Smith | | Andy | Smith | | Albert | Smith | +-----------+----------+ 4 rows in set (0.00 sec)
mysql> SELECT firstname, lastname FROM master_name WHERE lastname = 'Smith'; +-----------+----------+ | firstname | lastname | +-----------+----------+ | John | Smith | | Jane | Smith | | Andy | Smith | | Albert | Smith | +-----------+----------+ 4 rows in set (0.01 sec)
There's also a handy operator called BETWEEN, which is useful with integer or data comparisons because it searches for results between a minimum and a maximum value. For example:
mysql> SELECT * FROM master_name WHERE name_id BETWEEN 1 AND 10; +---------+---------------------+---------------------+-----------+----------+ | name_id | name_dateadded | name_datemodified | firstname | lastname | +---------+---------------------+---------------------+-----------+----------+ | 1 | 2001-10-29 13:11:00 | 2001-10-29 13:11:00 | John | Smith | | 2 | 2001-10-29 13:11:00 | 2001-10-29 13:11:00 | Jane | Smith | | 3 | 2001-10-29 13:11:00 | 2001-10-29 13:11:00 | Jimbo | Jones | | 4 | 2001-10-29 13:11:00 | 2001-10-29 13:11:00 | Andy | Smith | | 7 | 2001-10-29 14:16:21 | 2001-10-29 14:16:21 | Chris | Jones | +---------+---------------------+---------------------+-----------+----------+ 5 rows in set (0.00 sec)
Other operators are logical operators, which allow you to use multiple comparisons within your WHERE clause. The basic logical operators are AND and OR. When using AND, all comparisons in the clause must be true in order to retrieve results, while using OR allows a minimum of one comparison to be true.
For example, if you want to find records in the master_name table for people named John Smith, use
mysql> SELECT * FROM master_name WHERE firstname = 'John' -> AND lastname = 'Smith'; +---------+---------------------+---------------------+-----------+----------+ | name_id | name_dateadded | name_datemodified | firstname | lastname | +---------+---------------------+---------------------+-----------+----------+ | 1 | 2001-10-29 13:11:00 | 2001-10-29 13:11:00 | John | Smith | +---------+---------------------+---------------------+-----------+----------+ 1 row in set (0.00 sec)
If you want to find records for people with the last name of Smith or Jones, use
mysql> SELECT * FROM master_name WHERE lastname = 'Smith' OR lastname = 'Jones'; +---------+---------------------+---------------------+-----------+----------+ | name_id | name_dateadded | name_datemodified | firstname | lastname | +---------+---------------------+---------------------+-----------+----------+ | 1 | 2001-10-29 13:11:00 | 2001-10-29 13:11:00 | John | Smith | | 2 | 2001-10-29 13:11:00 | 2001-10-29 13:11:00 | Jane | Smith | | 3 | 2001-10-29 13:11:00 | 2001-10-29 13:11:00 | Jimbo | Jones | | 4 | 2001-10-29 13:11:00 | 2001-10-29 13:11:00 | Andy | Smith | | 7 | 2001-10-29 14:16:21 | 2001-10-29 14:16:21 | Chris | Jones | | 43 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | Albert | Smith | +---------+---------------------+---------------------+-----------+----------+ 6 rows in set (0.00 sec)
In order to keep your statements tidy—and in the case of complex queries this will help clarify your logic—surround the conditions within parentheses. The above queries could be re-written as
SELECT * FROM master_name WHERE ((firstname = 'John') AND (lastname = 'Smith')); SELECT * FROM master_name WHERE ((lastname = 'Smith') OR (lastname = 'Jones'));
String Comparison Using LIKE
You were introduced to matching strings within a WHERE clause by using = or !=, but there's another useful operator for string comparisons: LIKE. This operator uses two characters as wildcards in pattern matching.
%—Matches multiple characters
_—Matches exactly one character
If you want to find records in the master_name table where the first name of the person starts with the letter "A", use
mysql> SELECT name_id, firstname, lastname FROM master_name -> WHERE firstname LIKE 'A%'; +---------+-----------+----------+ | name_id | firstname | lastname | +---------+-----------+----------+ | 43 | Albert | Smith | | 4 | Andy | Smith | | 45 | Anna | Bell | +---------+-----------+----------+ 3 rows in set (0.00 sec)
If you wanted to find only people named "Anna" or "Anne", use
mysql> SELECT name_id, firstname, lastname FROM master_name -> WHERE firstname LIKE 'Ann_'; +---------+-----------+----------+ | name_id | firstname | lastname | +---------+-----------+----------+ | 45 | Anna | Bell | +---------+-----------+----------+ 1 row in set (0.00 sec)
In this case, the _ wildcard is replacing just the "e" or "a". Anyone in your table named "Annabelle" would not be selected.