34,333
edits
Changes
→Filtering using the OR Operator
== Filtering 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>
SELECT * from products WHERE prod_price < 100 OR prod_price > 200;
</pre>
The resulting output from executing the above SQL statement would contain all products ''except'' those priced between $100 and $200:
<pre>
+-----------+----------------------------+--------------------------+------------+
| prod_code | prod_name | prod_desc | prod_price |
+-----------+----------------------------+--------------------------+------------+
| 3 | Microsoft 10-20 Keyboard | Ergonomic Keyboard | 49 |
| 4 | EasyTech Mouse 7632 | Cordless Mouse | 49 |
| 5 | Dell XPS 400 | Desktop PC | 999 |
| 6 | Buffalo AirStation Turbo G | Wireless Ethernet Bridge | 60 |
| 8 | Apple iPhone 8Gb | Smart Phone | 399 |
+-----------+----------------------------+--------------------------+------------+
5 rows in set (0.00 sec)
</pre>
== Filtering 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>
SELECT * from products WHERE prod_name = 'Microsoft 10-20 Keyboard' AND prod_price < 30;
</pre>
Since we have no such keyboards in our table which meet the price criteria we get no results. If the customer decides to pay more we can change our search to find a suitable item:
<pre>
SELECT * from products WHERE prod_name = 'Microsoft 10-20 Keyboard' AND prod_price < 50;
</pre>
This time we find what the customer needs and the price she is willing to pay:
<pre>
+-----------+--------------------------+-------------------+------------+
| prod_code | prod_name | prod_desc | prod_price |
+-----------+--------------------------+-------------------+------------+
| 3 | Microsoft 10-20 Keyboard | Ergonomic Keyboard | 49 |
+-----------+--------------------------+-------------------+------------+
1 row in set (0.00 sec)
</pre>
