AND & OR in DBMS

AND & OR in DBMS

 

In this article, we will learn about AND & OR in DBMS.

  • Sometimes user requires more than one condition for filtering the data, this purpose is served by AND / OR clauses
  • The AND  and  OR operators are used with the where clause for precise filtration of data from the database tables by combining more than one condition along with select, update and delete queries
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

EMPNOENAMEJOBMGRHIREDATESALDEPTNO
7839KINGPRESIDENT17-NOV-81500010
7698BLAKEMANAGER783901-MAY-81285030
7782CLARKMANAGER783909-JUN-81245010
7566JONESMANAGER783902-APR-81297520
7788SCOTTANALYST756619-APR-87300020
7902FORDANALYST756603-DEC-81300020
7369SMITHCLERK790217-DEC-8080020

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.

EMPNOENAMEJOBMGRHIREDATESALDEPTNO
7698BLAKEMANAGER783901-MAY-81285030
7566JONESMANAGER783902-APR-81297520

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 

EMPNOENAMEJOBMGRHIREDATESALDEPTNO
7698BLAKEMANAGER783901-MAY-81285030
7782CLARKMANAGER783909-JUN-81245010
7566JONESMANAGER783902-APR-81297520
7788SCOTTANALYST756619-APR-87300020
7902FORDANALYST756603-DEC-81300020

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.

EMPNOENAMEJOBMGRHIREDATESALDEPTNO
7839KINGPRESIDENT17-NOV-81500010
7698BLAKEMANAGER783901-MAY-81285030
7782CLARKMANAGER783909-JUN-81245010

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.

Learn more about DBMS here on this page.