Finding Rows That Satisfy Multiple Conditions: SQL query by example

Problem

You want to return in the sql query 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 $2,000:

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 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 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

Вас заинтересует / Intresting for you:

A Brief History of SQL 5400 views Александров Попков Wed, 17 Oct 2018, 15:04:29

Referencing an Aliased Column ... 6683 views Денис Wed, 14 Jul 2021, 12:59:03

SQL: how to determine which ro... 1396 views Денис Tue, 06 Jul 2021, 18:47:53

How to Retrieving a Subset of ... 1700 views Денис Wed, 14 Jul 2021, 04:45:37