Advanced MySQL Data Filtering - AND, OR, NOT and IN
| Previous | Table of Contents | Next |
| Using WHERE to Filter MySQL Data | MySQL Wildcard Filtering using LIKE |
| Purchase and download the full PDF and ePub versions of this MySQL eBook for only $9.99
|
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:
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 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:
SELECT * from products WHERE prod_name = 'Microsoft 10-20 Keyboard' AND prod_price < 30;
Since we have no such keyboards in our table that 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)
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:
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 | Ergonomic Keyboard| 49 | | 4 | EasyTech Mouse 7632 | Cordless Mouse | 49 | +-----------+--------------------------+-------------------+------------+ 2 rows in set (0.00 sec)
Understanding Operator Precedence
When combining operators it is important to understand something called operator precedence, which 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:
SELECT prod_desc FROM products WHERE prod_name = 'WildTech 250Gb 1700' OR prod_name = 'Moto Razr' AND prod_price < 100;
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:
SELECT prod_desc FROM products WHERE (prod_name = 'WildTech 250Gb 1700' OR prod_name = 'Moto Razr') AND prod_price < 100;
The OR expression contained in the parentheses will now be executed before the AND expression.
Specifying a Range of Conditions using the IN Clause
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:
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)
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 very easily 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:
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)
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 using wildcards.
| Purchase and download the full PDF and ePub versions of this MySQL eBook for only $9.99
|
| Previous | Table of Contents | Next |
| Using WHERE to Filter MySQL Data | MySQL Wildcard Filtering using LIKE |
