Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-01 |
|
|
View PDF |
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.
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-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;