Finding Rows That Satisfy Multiple Conditions

Problem
You want to return rows that satisfy multiple conditions.

Solution
Use the WHERE clause along with the OR and AND clauses. For example, if you would like to find all the employees in department 10, along with any employees who earn a commission, along with any employees in department 20 who earn at most $2000:

1 select *
2 from emp
3 where deptno = 10
4 or comm is not null
5 or sal <= 2000 and deptno=20 Discussion
You can use a combination of AND, OR, and parenthesis to return rows that satisfy multiple conditions. In the solution example, the WHERE clause finds rows such that:

the DEPTNO is 10, or

the COMM is NULL, or

the salary is $2000 or less for any employee in DEPTNO 20.

The presence of parentheses causes conditions within them to be evaluated together.

For example, consider how the result set changes if the query was written with the parentheses as shown below:

select *
from emp
where
{
deptno = 10
or comm is not null
or sal <= 2000 }
and deptno=20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ------ ----- ----- ----------- ----- ------- ------
7369 SMITH CLERK 7902 17-DEC-1980 800 20
7876 ADAMS CLERK 7788 12-JAN-1983 1100 20