34,333
edits
Changes
no edit summary
</pre>
This time we find what the customer needs ''and '' the price she is willing to pay:
<pre>
1 row in set (0.00 sec)
</pre>
== Combining AND and OR Operators ==
A SELECT statement with a ''WHERE'' clause can combine any number of ''AND'' and ''OR'' operators to create complex filtering requirements. For example, we can combine operators to find either a mouse or a keyboard that costs $49:
<pre>
SELECT * from products WHERE prod_name = 'Microsoft 10-20 Keyboard' OR prod_name = 'EasyTech Mouse 7632' AND prod_price = 49;
+-----------+--------------------------+-------------------+------------+
| prod_code | prod_name | prod_desc | prod_price |
+-----------+--------------------------+-------------------+------------+
| 3 | Microsoft 10-20 Keyboard | Ergonmoc Keyboard | 49 |
| 4 | EasyTech Mouse 7632 | Cordless Mouse | 49 |
+-----------+--------------------------+-------------------+------------+
2 rows in set (0.00 sec)
</pre>
== Operator Evaluation Order ==
When combining operators it is important to understand something cvalled operator precedence, which referes to the order in which operators in the same statement are evaluated. By default MySQL evaluates ''AND'' expressions before ''OR'' expressions regardless of whether the ''OR'' appears before the ''AND'' when reading the statement from left to right. This means, for example, that the following SQL statement will evaluate the ''AND'' expression before it evaluates the ''OR'':
<pre>
SELECT prod_desc FROM products WHERE prod_name = 'WildTech 250Gb 1700' OR prod_name = 'Moto Razr' AND prod_price < 100;
</pre>
To change the operator precedence (in this case to cause the ''OR'' expression to be evaluated first), simply surround the ''OR'' expression with parentheses as follows:
<pre>
SELECT prod_desc FROM products WHERE (prod_name = 'WildTech 250Gb 1700' OR prod_name = 'Moto Razr') AND prod_price < 100;
</pre>
The ''OR'' expression contained in the parentheses will now be executed before the ''AND'' expression.
== Specifying a Range of Conditions using IN ==
The ''IN'' operator allows a range of filter criteria to be specified in a WHERE clause, all contained in parentheses and comma separated.
For example, imagine we need to list all products in our database that have a price of either $49, $100 or $999. Obviously we could write a statement that uses a series of ''OR'' expressions. Whilst this would ultimately work a much quicker way is to provide a list of desired prices using the ''IN'' clause:
<pre>
SELECT * from products WHERE prod_price IN (49, 100, 999); +-----------+--------------------------+-------------------+------------+
| prod_code | prod_name | prod_desc | prod_price |
+-----------+--------------------------+-------------------+------------+
| 3 | Microsoft 10-20 Keyboard | Ergonmoc Keyboard | 49 |
| 4 | EasyTech Mouse 7632 | Cordless Mouse | 49 |
| 5 | Dell XPS 400 | Desktop PC | 999 |
+-----------+--------------------------+-------------------+------------+
3 rows in set (0.00 sec)
</pre>
== Using the NOT Operator ==
The final operator to look at in this chapter is the ''NOT'' operator. The ''NOT'' operator is used to negate the result of an expression and is of particular use when using the ''IN'' operator. For example, we could vbery easy change our previous ''IN'' example so that it lists all the products in our table that do NOT cost $49, $100 or $999 simply by used a ''NOT IN'' operator combination:
<pre>
mysql> SELECT * from products WHERE prod_price NOT IN (49, 100, 999);
+-----------+----------------------------+-----------------------------+------------+
| prod_code | prod_name | prod_desc | prod_price |
+-----------+----------------------------+-----------------------------+------------+
| 1 | WildTech 250Gb 1700 | SATA Disk Drive | 120 |
| 2 | Moto Razr | Mobile Phone | 200 |
| 6 | Buffalo AirStation Turbo G | Wireless Ethernet Bridge | 60 |
| 7 | Apple iPod Touch | Portable Music/Movie Player | 199 |
| 8 | Apple iPhone 8Gb | Smart Phone | 399 |
+-----------+----------------------------+-----------------------------+------------+
5 rows in set (0.00 sec)
</pre>
== Summary ==
In this chapter we have extended our knowledge of data filtering by exploring the use of the ''AND'', ''NOT'', ''OR'', and ''IN'' operators. In the next chapter we will look at even more flexible filtering [[MySQL Wildcard Filtering||using wildcards]].
