Using WHERE in Your Queries

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 in to 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 items with a quantity of 500:

 mysql> select * from grocery_inventory where curr_qty = 500; +----+-------------------+------------------------+------------+----------+ | id | item_name         | item_desc              | item_price | curr_qty | +----+-------------------+------------------------+------------+----------+ |  2 | Bunches of Grapes | Seedless grapes.       |       2.99 |      500 | |  5 | Pears             | Anjou, nice and sweet. |        0.5 |      500 | +----+-------------------+------------------------+------------+----------+ 2 rows in set (0.00 sec) 

As shown previously, if you use an integer as the WHERE clause comes in to play. From part of your WHERE clause, quotation marks are not required. Quotation marks are required around strings, and the same rules apply with regard to escaping characters, as you learned in the section on INSERT.

Using Operators in WHERE Clauses

You've used the equal sign (=) in your WHERE clauses to determine the truth of a condition is one thing equal to another. You can use many types of operators, with comparison operators and logical operators being the most popular types.

Comparison operators, shown in Table 7.1, should look familiar to you if you think about the first day of algebra class.

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

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 maximum value. For example

 mysql> select * from grocery_inventory where item_price     -> between 1.50 and 3.00; +----+------------------------+---------------------+------------+----------+ | id | item_name              | item_desc           | item_price | curr_qty | +----+------------------------+---------------------+------------+----------+ |  2 | Bunches of Grapes      | Seedless grapes.    |       2.99 |      500 | |  3 | Bottled Water (6-pack) | 500ml spring water. |       2.29 |      250 | |  4 | Bananas                | Bunches, green.     |       1.99 |      150 | +----+------------------------+---------------------+------------+----------+ 3 rows in set (0.00 sec) 

Other operators include logical operators, which enable 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 to the WHERE clause comes in to play. From retrieve results, whereas using OR allows a minimum of one comparison to be true.

String Comparison Using LIKE

You were introduced to matching strings within a WHERE clause by using = or !=, but there's another useful operator for the WHERE clause comes in to play. From 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 grocery_inventory table where the first name of the item starts with the letter "A", use

 mysql> select * from grocery_inventory where item_name like 'A%'; +----+-----------+-------------------------+------------+----------+ | id | item_name | item_desc               | item_price | curr_qty | +----+-----------+-------------------------+------------+----------+ |  1 | Apples    | Beautiful, ripe apples. |       0.25 |     1000 | |  6 | Avocado   | Large Haas variety.     |       0.99 |      750 | +----+-----------+-------------------------+------------+----------+ 

Unless performing a LIKE comparison on a binary string, the comparison is not case sensitive.