MariaDB Crash Course - Ben Forta [22]
* * *
Using the IN Operator
Parentheses have another different use in WHERE clauses. The IN operator is used to specify a range of conditions, any of which can be matched. IN takes a comma-delimited list of valid values, all enclosed within parentheses. The following example demonstrates this:
Input
SELECT prod_name, prod_price
FROM products
WHERE vend_id IN (1002,1003)
ORDER BY prod_name;
Output
+----------------+------------+
| prod_name | prod_price |
+----------------+------------+
| Bird seed | 10.00 |
| Carrots | 2.50 |
| Detonator | 13.00 |
| Fuses | 3.42 |
| Oil can | 8.99 |
| Safe | 50.00 |
| Sling | 4.49 |
| TNT (1 stick) | 2.50 |
| TNT (5 sticks) | 10.00 |
+----------------+------------+
Analysis
The SELECT statement retrieves all products made by vendor 1002 and vendor 1003. The IN operator is followed by a comma-delimited list of valid values, and the entire list must be enclosed within parentheses.
If you are thinking that the IN operator accomplishes the same goal as OR, you are right. The following SQL statement accomplishes the exact same thing as the previous example:
Input
SELECT prod_name, prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003
ORDER BY prod_name;
Output
+---------------------+------------+
| prod_name | prod_price |
+---------------------+------------+
| 12 inch teddy bear | 8.9900 |
| 18 inch teddy bear | 11.9900 |
| 8 inch teddy bear | 5.9900 |
| Bird bean bag toy | 3.4900 |
| Fish bean bag toy | 3.4900 |
| Rabbit bean bag toy | 3.4900 |
| Raggedy Ann | 4.99000 |
+---------------------+------------+
Why use the IN operator? The advantages are
• When you are working with long lists of valid options, the IN operator syntax is far cleaner and easier to read.
• The order of evaluation is easier to manage when IN is used (as there are fewer operators used).
• IN operators almost always execute more quickly than lists of OR operators (although you’ll not see any performance difference with very short lists like the ones used here).
• The biggest advantage of IN is that the IN operator can contain another SELECT statement, enabling you to build highly dynamic WHERE clauses. We look at this in detail in Chapter 14, “Working with Subqueries.”
* * *
New Term: IN
A keyword used in a WHERE clause to specify a list of values to be matched using an OR comparison.
* * *
Using the NOT Operator
The WHERE clause’s NOT operator has one function and one function only—NOT negates whatever condition comes next.
* * *
New Term: NOT
A keyword used in a WHERE clause to negate a condition.
* * *
The following example demonstrates the use of NOT. To list the products made by all vendors except vendors 1002 and 1003, you can use the following:
Input
SELECT prod_name, prod_price
FROM products
WHERE vend_id NOT IN (1002,1003)
ORDER BY prod_name;
Output
+--------------+------------+
| prod_name | prod_price |
+--------------+------------+
| .5 ton anvil | 5.99 |
| 1 ton anvil | 9.99 |
| 2 ton anvil | 14.99 |
| JetPack 1000 | 35.00 |
| JetPack 2000 | 55.00 |
+--------------+------------+
Analysis
The NOT here negates the condition that follows it; so instead of matching vend_id to 1002 or 1003, MariaDB matches vend_id to anything that is not 1002 or 1003.
So why use NOT? Well, for simple WHERE clauses, there really is no advantage to using NOT. NOT is useful in more complex clauses. For example, using NOT in conjunction with an IN operator makes it simple to find all rows that do not match a list of criteria.
* * *
Note: NOT in MariaDB
MariaDB supports the use of NOT to negate IN, BETWEEN, and EXISTS clauses. This is different from most other DBMSs that allow NOT to be used to negate any conditions.
* * *
Summary
This chapter picked up where the last chapter left off and taught you how to combine WHERE clauses with the AND and OR operators. You also learned how to explicitly