You can exclude data by using the NOT keyword in the WHERE clause of the query.
ExamplesThe following example selects all divisions that are not EASTERN or WESTERN.
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)