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 match multiple conditions, you can use the WHERE clause along with the OR and AND operators. For example, if you want to find all employees in department 10, as well as any employees who earn a commission, and also any employees in department 20 who make at most $2,000, you can use a query like this:
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 the AND, OR, and parentheses operators in the WHERE clause to specify multiple conditions for a query. This allows you to fine-tune the criteria for the rows that you want to retrieve from a database table.
In the example provided, the WHERE clause uses the AND and OR operators, along with parentheses, to find rows that satisfy the following conditions:
- The employee is in department 10, OR
- The employee earns a commission, OR
- The employee is in department 20 AND makes at most $2,000.
The presence of parentheses causes the conditions within them to be evaluated together. For example, if the query was written without 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 |
Code Runner
Copy the below queries in the code runner to see the result
SELECT * FROM Staff
WHERE deptno = 10
OR commission is not null
OR sal <= 2000 and deptno=20
SELECT * FROM Staff
WHERE( deptno = 10
OR commission is not null
OR sal <= 2000
)
AND deptno=20