skip to main content
Operators : Logical operators
 

Logical operators

A logical operator combines the results of two component conditions to produce a single result or to invert the result of a single condition. The following table describes the supported logical operators.
 
Table 8: Logical operators 
Operator
Purpose
Example
NOT
Returns TRUE if the following condition is FALSE. Returns FALSE if it is TRUE. If it is UNKNOWN, it remains UNKNOWN.
SELECT * FROM emp WHERE NOT (job IS NULL);
SELECT * FROM emp WHERE NOT (sal BETWEEN 1000 AND 2000);
AND
Returns TRUE if both component conditions are TRUE. Returns FALSE if either is FALSE; otherwise, returns UNKNOWN.
SELECT * FROM emp WHERE job = 'CLERK' AND deptno = 10;
OR
Returns TRUE if either component condition is TRUE. Returns FALSE if both are FALSE; otherwise, returns UNKNOWN.
SELECT * FROM emp WHERE job = 'CLERK' OR deptno = 10;
For example, in the WHERE clause of the following SELECT statement, the AND logical operator is used to ensure that only those hired before 1984 and earning more than $1000 a month are returned:
SELECT * FROM emp
WHERE hiredate < {ts '01-02-1984 10:10:10'} AND sal > 1000;