34,333
edits
Changes
no edit summary
In the previous chapter of [[MySQL Essentials]] we looked at some basic filtering of retrieved data using the ''WHERE'' clause of the ''SELECT'' statement. While the basics covered simple filtering techniques, there are often situations where more sophisticated filtering is required. To address this need, the WHERE clause allows multiple conditions to be defined in a single SELECT statement using ''AND'', ''OR'' ''IN'' and ''NOT'' operators.
== Filtering Data Using the ''OR '' Operator ==
In the previous chapter we looked at filtering the data retrieved using a ''SELECT'' statement based on single criteria. For example, we retrieved all the products in a table where the product price was below a particular threshold. Imagine, however, if you wanted to retrieve all the rows from a table where the product matched either one value or another. This can be achieved using the ''WHERE'' clause in combination with the ''OR'' operator. Say, for example that we need to list all products in our sample database that cost less than $100 or greater than $200. The SQL statement to achieve this would read as follows:
</pre>
== Filtering Data Using the ''AND '' Operator ==
Another operator used to combine filtering criteria when using the ''WHERE'' clause is the AND operator. The ''AND'' operator selects rows based on the requirement that meet multiple requirements (as opposed to the "either or" approach of the ''OR'' operator). Suppose, for example, that we need to find a "Microsoft 10-20 Keyboard" that costs less than $30. TO do so we would construct a SELECT statement as follows:
</pre>
== Understanding Operator Evaluation Order Precedence ==
When combining operators it is important to understand something cvalled called operator precedence, which referes refers 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>
</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:
