AND & OR in DBMS

AND clause

Definition: The AND results true only when all the conjunction of conditions specified after the where clause are satisfied

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

Consider a sample table emp

Display the records of those employees who are working as a manager and getting salary greater than 2500 /-

select * from emp
where job='MANAGER' AND sal>2500;

O/P 2 rows selected. Here two conditions first employee must be a manager at the same time he must be getting salary greater than 2500 then only condition becomes true and the record gets displayed.

OR clause

Definition: Among multiple conditions specified in the where clause  the transaction is performed if any of the condition becomes true

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...

Display all employees records who are working as analyst and managers

select * from emp
where JOB='ANALYST' OR JOB='MANAGER';

O/P 5 rows selected  In the example above, the data is fetched if  the employee is an analyst or a manager i.e data is fetched even if any one condition is satisfied.

Combining AND and OR

In the where clause it is possible that a condition is specified as a conjunction of both AND  & OR 

Syntax:

SELECT * FROM table_name 
WHERE condition1 AND|OR (condition2 AND|OR condition3....);

Example

select * from emp
where (sal>1500 OR job='MANAGER') AND (deptno=10 OR deptno=30);

O/P 3 rows selected. The above query displays the details of employees  having a salary greater than 1500/- or if he is a manager which is exclusively from 10th dept and 30th departments.