Ø 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);