Difference between revisions of "Advanced MySQL Data Filtering - AND, OR, NOT and IN"

Revision as of 19:15, 12 October 2007

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 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:

SELECT * from products WHERE prod_price < 100 OR prod_price > 200;

The resulting output from executing the above SQL statement would contain all products except those priced between $100 and $200:

+-----------+----------------------------+--------------------------+------------+
| 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)

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:

SELECT * from products WHERE prod_name = 'Microsoft 10-20 Keyboard' AND prod_price < 30;

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:

SELECT * from products WHERE prod_name = 'Microsoft 10-20 Keyboard' AND prod_price < 50;

This time we find what the customer needs and the price she is willing to pay:

+-----------+--------------------------+-------------------+------------+
| prod_code | prod_name                | prod_desc         | prod_price |
+-----------+--------------------------+-------------------+------------+
|         3 | Microsoft 10-20 Keyboard | Ergonomic Keyboard|         49 |
+-----------+--------------------------+-------------------+------------+
1 row in set (0.00 sec)