MySQL —

Using the NOT Operator

NOT is a keyword used in a WHERE clause to negate a condition.

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

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:

SELECT prod_name, prod_price
FROM products
WHERE vend_id NOT IN (1002,1003)
ORDER BY prod_name;

This statement returned the following output:

+------------+------------+
| prod_name  | prod_price |
+------------+------------+
| product 2  |       3.99 |
| product 3  |       8.99 |
| product 6  |       8.99 |
| product 7  |       7.00 |
| product 8  |       1.80 |
| product 10 |       4.75 |
+------------+------------+

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.

NOT in 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.