How to find rows that satisfy multiple conditions in SQL?

The following tables named Staff and Department are being used for the query.

Staff Table

empno ename job mgr hiredate sal commission deptno
7369 ALI CLERK 7902 1980-12-17 800.00 NULL 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-04-02 2975.00 NULL 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 NULL 30
7782 CLARK MANAGER 7839 1981-06-09 2450.00 NULL 10
7788 RAHUL ANALYST 7566 1982-12-09 3000.00 NULL 20
7839 RAJ PRESIDENT NULL 1981-11-17 5000.00 NULL 10
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
7876 ADAM CLERK 7788 1983-01-12 1100.00 NULL 20
7900 JAMES CLERK 7698 1981-12-03 950.00 NULL 30
7902 ARHAM ANALYST 7566 1981-12-03 3000.00 NULL 20
7934 MILLER CLERK 7782 1982-01-23 1300.00 NULL 10

Department Table

deptno dname loc
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

To return rows that satisfy multiple conditions.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 make at most $2,000

 SELECT * FROM Staff
 WHERE deptno = 10
 OR commission is not null
 OR sal <= 2000 and deptno=20

Output

empno ename job mgr_no hiredate sal commission deptno
7369 ALI CLERK 7902 1980-12-17 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
7782 CLARK MANAGER 7839 1981-06-09 2450 10
7839 RAJ PRESIDENT 1981-11-17 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
7876 ADAM CLERK 7788 1983-01-12 1100 20
7934 MILLER CLERK 7782 1982-01-23 1300 10

You can use a combination of AND, OR, and parentheses to return rows that satisfy
multiple conditions. In the solution example, the WHERE clause finds rows such
that:

  • The DEPTNO is 10
  • The COMM is not NULL
  • The salary is $2,000 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 here:

SELECT * FROM Staff
WHERE( deptno = 10
OR commission is not null
OR sal <= 2000
 )
AND deptno=20

Output

empno ename job mgr_no hiredate sal commission deptno
7369 ALI CLERK 7902 1980-12-17 800 20
7876 ADAM CLERK 7788 1983-01-12 1100 20