Database Reference
In-Depth Information
▼
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
opera-
tor 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
oper-
ators (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.
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:
