Using the NOT Operator

Using the NOT Operator

The WHERE clause's NOT operator has one function and one function onlyNOT negates whatever condition comes next.

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, MySQL 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 MySQL MySQL supports the use of NOT to negate IN, BETWEEN, and EXISTS clauses. This is quite different from most other DBMSs that allow NOT to be used to negate any conditions.