Using the NOT Operator

The WHERE clause's NOT operator has one function and one function only—NOT negates whatever condition comes next. Because NOT is never used by itself (it is always used in conjunction with some other operator), its syntax is a little different from all other operators. Unlike other operators, the NOT keyword can be used before the column to filter on, not just after it.

The following example demonstrates the use of NOT. To list the products made by all vendors except vendor DLL01, you can write the following:

SELECT prod_name
FROM Products
WHERE NOT vend_id  = 'DLL01'
ORDER BY prod_name;

prod_name
------------------
12 inch teddy bear
18 inch teddy bear
8 inch teddy bear

The NOT here negates the condition that follows it; so instead of matching vend_id to DLL01, the DBMS matches vend_id to anything that is not DLL01.

The preceding example could have also been accomplished using the <> operator, as follows:

SELECT prod_name
FROM Products
WHERE vend_id  <> 'DLL01'
ORDER BY prod_name;

prod_name
------------------
12 inch teddy bear
18 inch teddy bear
8 inch teddy bear

Why use NOT? Well, for simple WHERE clauses such as the ones shown here, 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.