A logical condition combines two conditions to produce a single result or reverse the result of a single condition.
Note
For more information about logical operators, see Logical operators.
Logical operators include NOT, AND, OR, and XOR.
Logical condition NOT
The logical condition NOT means "not" and can reverse the result of a single condition.
Decision rules
If the condition is FALSE, return TRUE. If the condition is TRUE, return FALSE. If it is UNKNOWN, return UNKNOWN.
Examples
obclient> DROP TABLE IF EXISTS emp;
obclient> CREATE TABLE emp (
empno INT,
dept_id INT,
job_id VARCHAR(20),
salary DECIMAL(10, 2)
);
obclient> INSERT INTO emp VALUES
(1, 10, 'PU_CLERK', 15000),
(2, 32, 'PU_CLERK', 12000),
(3, 10, NULL, 9000);
obclient> SELECT * FROM emp WHERE NOT (job_id IS NULL) ORDER BY empno;
obclient> SELECT * FROM emp WHERE NOT (salary BETWEEN 11000 AND 22000) ORDER BY empno;
Logical condition AND
The logical operator AND indicates "and" and is used to connect two conditions.
Decision rules
If both conditions are TRUE, TRUE is returned. If either condition is FALSE, FALSE is returned. Otherwise, UNKNOWN is returned.
Examples
obclient> DROP TABLE IF EXISTS emp;
obclient> CREATE TABLE emp (
empno INT,
dept_id INT,
job_id VARCHAR(20),
salary DECIMAL(10, 2)
);
obclient> INSERT INTO emp VALUES
(1, 32, 'PU_CLERK', 15000),
(2, 10, 'SA_MAN', 12000);
obclient> SELECT * FROM emp WHERE job_id = 'PU_CLERK' AND dept_id = 32 ORDER BY empno;
Logical condition OR
The logical operator OR indicates "or", meaning any one of the conditions must be true.
Decision rule
If any condition is TRUE, return TRUE. If both are FALSE, return FALSE. Otherwise, return UNKNOWN.
Examples
obclient> DROP TABLE IF EXISTS emp;
obclient> CREATE TABLE emp (
empno INT,
dept_id INT,
job_id VARCHAR(20),
salary DECIMAL(10, 2)
);
obclient> INSERT INTO emp VALUES
(1, 10, 'SA_MAN', 15000),
(2, 32, 'PU_CLERK', 12000);
obclient> SELECT * FROM emp WHERE job_id = 'PU_CLERK' OR dept_id = 10 ORDER BY empno;
Logical condition XOR
The logical operator XOR represents "exclusive OR".
Decision rules
Returns TRUE if and only if one of the conditions is TRUE and the other is FALSE. Returns FALSE if both conditions are TRUE or both are FALSE.
Examples
obclient> DROP TABLE IF EXISTS emp;
obclient> CREATE TABLE emp (
empno INT,
dept_id INT,
job_id VARCHAR(20),
salary DECIMAL(10, 2)
);
obclient> INSERT INTO emp VALUES
(1, 10, 'PU_CLERK', 15000),
(2, 32, 'SA_MAN', 12000);
obclient> SELECT * FROM emp WHERE (job_id = 'PU_CLERK') XOR (dept_id = 10) ORDER BY empno;