Skip Headers
Oracle® Database SQL Reference
10g Release 2 (10.2)

Part Number B14200-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

Logical Conditions

A logical condition combines the results of two component conditions to produce a single result based on them or to invert the result of a single condition. Table 7-4 lists logical conditions.

Table 7-4 Logical Conditions

Type of Condition Operation Examples
NOT 
Returns TRUE if the following condition is FALSE. Returns FALSE if it is TRUE. If it is UNKNOWN, then it remains UNKNOWN.
SELECT *
  FROM employees
  WHERE NOT (job_id IS NULL);
SELECT *
  FROM employees
  WHERE NOT 
  (salary BETWEEN 1000 AND 2000);
AND 
Returns TRUE if both component conditions are TRUE. Returns FALSE if either is FALSE. Otherwise returns UNKNOWN.
SELECT *
  FROM employees
  WHERE job_id = 'PU_CLERK'
  AND department_id = 30;
OR 
Returns TRUE if either component condition is TRUE. Returns FALSE if both are FALSE. Otherwise returns UNKNOWN.
SELECT *
  FROM employees
  WHERE job_id = 'PU_CLERK'
  OR department_id = 10;

Table 7-5 shows the result of applying the NOT condition to an expression.

Table 7-5 NOT Truth Table

-- TRUE FALSE UNKNOWN
NOT FALSE TRUE UNKNOWN

Table 7-6 shows the results of combining the AND condition to two expressions.

Table 7-6 AND Truth Table

AND TRUE FALSE UNKNOWN
TRUE TRUE FALSE UNKNOWN
FALSE FALSE FALSE FALSE
UNKNOWN UNKNOWN FALSE UNKNOWN

For example, in the WHERE clause of the following SELECT statement, the AND logical condition is used to ensure that only those hired before 1989 and earning more than $2500 a month are returned:

SELECT * FROM employees
WHERE hire_date < TO_DATE('01-JAN-1989', 'DD-MON-YYYY')
   AND salary > 2500;

Table 7-7 shows the results of applying OR to two expressions.

Table 7-7 OR Truth Table

OR TRUE FALSE UNKNOWN
TRUE TRUE TRUE TRUE
FALSE TRUE FALSE UNKNOWN
UNKNOWN TRUE UNKNOWN UNKNOWN

For example, the following query returns employees who have a 40% commission rate or a salary greater than $20,000:

SELECT employee_id FROM employees
   WHERE commission_pct = .4 OR salary > 20000;