The logical operator NOT allows the user to express conditions that
are best expressed in a negative way. In essence, it reverses the
logical value of a condition on which it operates. That is, it
accepts all rows except those that satisfy the condition. You write
the conditional expression with the keyword NOT preceding the
WHERE NOT condition
The condition can be a simple condition or a condition containing ANDs and ORs. The compound condition using NOT is true if the condition following NOT is false; and the compound condition is false if the condition following NOT is true.
For example, suppose you are looking for all departments who are not
in the Corporate Division. You can write the conditional expression:
NOT (DIV = 'CORP')
Parentheses are optional but are included to improve readability of
If a department is in the Product Division, the program evaluates the condition in the following manner:
|Step 1: ||Original condition.|
|Step 2: || Substitute |
|Step 3: || Since |
| the condition |
|Step 4: true||NOT changes false to true,|
|the row is retrieved.|
NOT is typically used with logical operators such as IN, BETWEEN,
LIKE, etc., which will be covered in a later section.
In the query condition
NOT (DIV = 'CORP'), you are more likely
to write the condition as follows:
WHERE DIV != 'CORP'
In this query the `
!=' operator is used to show that
must not be equal to
Example: The NOT operator can be used with more than one expression. List all departments except those in the Corporate Division or those in the Product Divison.
Enter the statement:
SELECT DNAME, DIV
WHERE NOT (DIV = 'CORP' OR DIV = 'PROD') ;
NOT precedes the entire condition.
The result displayed will be:
DNAME DIV Information Systems Management INFO Corporate Library INFO Strategic Planning and Intelligence INFO
This statement retrieves the department and division name for all departments which are not Corporate or Product, revealing a division not yet retrieved in the previous searches, the Information Division.