NOT

Examples

The following example selects all divisions that are not EASTERN or WESTERN.

This query:

SELECT DEPTNUMB, LOCATION,
 DIVISION FROM Q.ORG
WHERE NOT
 (DIVISION = 'EASTERN' OR DIVISION = 'WESTERN')

Produces this report:

DEPTNUMB LOCATION       DIVISION
-------- -------------  ---------
     10  NEW YORK       CORPORATE
     42  CHICAGO        MIDWEST
     51  DALLAS         MIDWEST

To make it clear what the NOT condition applies to, use parentheses. If you use NOT with AND or OR and you do not use parentheses, conditions that are preceded by NOT are negated before they are connected by AND or OR. For example, if A, B, and C are conditions, these two phrases are equivalent:

NOT A AND B OR C
((NOT A) AND B) OR C

With greater than, less than, or equals, NOT must precede the entire condition, as in WHERE NOT YEARS = 10. You can also negate the equal sign with the not symbol (¬).

These statements are correct:

• WHERE YEARS ¬ > 10
• WHERE NOT YEARS = 10

This statement is incorrect:

WHERE YEARS NOT = 10

The symbol ¬= is an alternative operator for < > (not equal to). It is an ANSI SQL operator. (If you are using remote data access, the preferred symbol is < >.)

You can use NOT NULL, NOT LIKE, NOT IN, or NOT BETWEEN; only in these cases can NOT follow the first part of the condition. For example:

WHERE YEARS IS NOT NULL

To select everyone whose salary is not between $17,000 and $21,000, use a query like the following query:

SELECT ID, NAME, SALARY
FROM Q.STAFF
WHERE SALARY NOT BETWEEN 17000 AND 21000

To select everyone who does not earn a salary less than $18,000 and also earns a commission of less than $500, use a query like the following query:

SELECT ID, NAME, SALARY, COMM
FROM Q.STAFF
WHERE NOT (SALARY < 18000 AND COMM < 500)

To select only managers in Q.STAFF who are not managers of departments in the Q.ORG table, use a query like the following query:

SELECT ID, NAME, DEPT
FROM Q.STAFF
WHERE JOB = 'MGR'
AND ID NOT IN (SELECT MANAGER FROM Q.ORG)