Database Reference
In-Depth Information
▼
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.
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 manage the order of evaluation, and how to use the
IN
and
NOT
operators.
