How to test for a false condition

You can use the NOT keyword to test for a false condition.

You can use the NOT keyword to select all rows for which the predicate is false (but not rows for which the predicate is unknown). The NOT keyword must precede the predicate.

Begin general-use programming interface information.

Example

To select all managers whose compensation is not greater than $40,000, use:

SELECT DEPT, EMPNO
  FROM EMP
  WHERE NOT (SALARY + COMM)> 40000 AND JOB = 'MGR'
  ORDER BY DEPT;

The following table contrasts WHERE clauses that use a NOT keyword with comparison operators and WHERE clauses that use only comparison operators. The WHERE clauses are equivalent.

Table 1. Equivalent WHERE clauses
Using NOT	Equivalent clause without NOT
WHERE NOT DEPTNO = 'A00'	WHERE DEPTNO <> 'A00'
WHERE NOT DEPTNO < 'A00'	WHERE DEPTNO>= 'A00'
WHERE NOT DEPTNO> 'A00'	WHERE DEPTNO <= 'A00'
WHERE NOT DEPTNO <> 'A00'	WHERE DEPTNO = 'A00'
WHERE NOT DEPTNO <= 'A00'	WHERE DEPTNO> 'A00'
WHERE NOT DEPTNO>= 'A00'	WHERE DEPTNO < 'A00'

You cannot use the NOT keyword directly preceding equality and inequality comparison operators.

Example

The following WHERE clause results in an error:

Wrong:  
WHERE DEPT NOT = 'A00'

Example

The following two clauses are equivalent:

Correct:
WHERE MGRNO NOT IN ('000010', '000020')
WHERE NOT MGRNO IN ('000010', '000020')

End general-use programming interface information.