Logical Operator NOT

Logical Operator `NOT`

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 condition:

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 the condition.

If a department is in the Product Division, the program evaluates the condition in the following manner:

Evaluation Process	Comments
Step 1: `NOT (DIV = 'CORP')`	Original condition.
Step 2: `NOT ('PROD' = 'CORP')`	Substitute `PROD` for `DIV`.
Step 3: `NOT` (false)`	Since `PROD` does not equal `CORP`,
	the condition `DIV = 'CORP'` is false.
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 DIV must not be equal to CORP.

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
     FROM    DEPARTMENT
     WHERE   NOT (DIV = 'CORP' OR DIV = 'PROD') ;

Note that 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.