Recipe1.3.Finding Rows That Satisfy Multiple Conditions

Recipe 1.3. 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