Logical Operators

Ø  The Logical operators combine the results of two component conditions to produce a single result.
Ø  The Logical operators are:
o   Logical conjunction operator à AND
o   Logical disjunction operator à OR
o   Logical negation operator à NOT
AND Operator:
Ø  It Returns TRUE if both or all component conditions are TRUE.
Ø  It Returns FALSE if either is FALSE, else returns unknown.
Truth Table:
AND
TRUE
FALSE
NULL
TRUE
T
F
NULL
FALSE
F
F
F
NULL
NULL
F
NULL
Ex:
  • Select Ename, Sal, Deptno, Job From Emp Where Deptno = 20 AND Job = ’MANAGER’;
  • Select Empno, Ename, Job, Sal From Emp Where sal >= 1100 AND Job = ’CLERK’;
  • Select Empno, Ename, Job, Sal From Emp Where Deptno = 10 AND Job = ’CLERK’;
  • Select Ename, Sal, Job From Emp Where Sal >= 1500 AND Sal > 5000;
  • Select Ename, Sal, Job From Emp Where (Sal >= 1500 AND Sal <=5000) AND Job = ’MANAGER’;
OR Operator:
Ø  It Returns TRUE if either of the component condition is TRUE.
Ø  It Returns FALSE if both are FALSE, else returns unknown.

Truth Table:
OR
TRUE
FALSE
NULL
TRUE
T
T
T
FALSE
T
F
NULL
NULL
T
NULL
NULL

Ex:
  • Select Ename, Sal, Deptno, Job From Emp where Deptno = 20 OR Job = ’MANAGER’;
  • Select Empno, Ename, Job, Sal From Emp where Sal >= 1100 OR Job =’CLERK’;
  • Select Empno, Ename, Job, Sal From Emp where Deptno = 10 OR Job =’MANAGER’;
  • Select Ename, Sal, Job From Emp where Sal >= 1500 OR Sal >=5000;
  • Select Ename, Sal, Job, Deptno From Emp Where Deptno = 10 OR Deptno=20;
  • Select Ename, Sal, Job From Emp Where Job = ’CLERK’ OR Job = ’MANAGER’;
  • Select Ename, Sal, Job From Emp where (sal <= 2500 OR Sal >= 5000) OR Job = ’MANAGER’;
NOT Operator:
Ø  It Returns TRUE if the following condition is FALSE.
Ø  It Returns FALSE if the following condition is TRUE.
Ø  If the condition is unknown, it returns unknown.
Truth Table:       
NOT
TRUE
FALSE
NULL
NOT
F
T
NULL

Ex:
  • Select Ename, Sal, Job From Emp where NOT Job = ’MANAGER’;
  • Select Ename, Sal, Job From Emp where NOT Sal > 5000;
  • Select Ename, Sal, Job From Emp where NOT Sal < 5000;
  • Select Ename, Sal, Hiredate From Emp where NOT Hiredate = ’20-FEB-81’;
  • Select Ename, Job, Sal, Deptno From Emp where NOT Job = ’SALESMAN’ AND Deptno = 30;
Combination of AND and OR Opertors:
  • Select Empno, Ename, Job, Sal From Emp where (sal > 1100 OR Job = ’CLERK’) AND Deptno = 20;
  • Select Empno, Ename, Job, Sal From Emp where (Deptno = 10 AND Job = ’MANAGER’) OR Sal >= 3000;
  • Select Empno, Ename, Job, Sal From Emp where (Deptno = 10 AND Job = ’MANAGER’) OR (Deptno = 20 AND Sal >= 3000);
Some Things to Note:
  • Select Ename, Sal, Job From Emp where Job > ’MANAGER’;
  • Select Ename, Sal, Job From Emp where Job < ’MANAGER’;
  • Select Ename, Sal, Hiredate From Emp where Hiredate > ’21-FEB-1981’;
  • Select Ename, Sal, Hiredate From Emp where Hiredate < ’21-FEB-1981’;
  • Select Ename, Sal, Hiredate From Emp where Hiredate <> ’21-FEB-1981’;
  • Select Ename, Sal, Hiredate From Emp where Job <>’CLERK’;
  • Select Ename, Sal, Job From Emp where NOT Job > ’MANAGER’;
  • Select Ename, Sal, Hiredate From Emp where NOT Hiredate = ’17-DEC-1980’;
  • Select Ename, Sal, Hiredate From Emp where NOT Hiredate > ’17-DEC-1980’;
  • Select Ename, Sal, Hiredate From Emp where NOT Hiredate > ’17-DECEMBER-1980’;
Rules of Operator Precedence:
Ø  The Default precedence order is:
o   All comparison operators
o   NOT operator
o   AND operator
o   OR operator
Ø  The precedence can be controlled using parenthesis.
Ex:

  • Select Ename, Deptno, Job, Sal From Emp where Deptno = 10 OR Deptno = 20 AND Job =’SALESMAN’ AND Sal > 2500 OR Sal < 1500;
  • Select Ename, Deptno, Job, Sal From Emp where Deptno = 10 OR (Deptno = 20 AND Job = ’SALESMAN’) AND (Sal > 2500 OR Sal < 1500);