Multiple search conditions within a WHERE clause

 

You can qualify your request further by coding a search condition that includes several predicates.

The search condition you specify can contain any of the comparison operators or the predicates BETWEEN, DISTINCT, IN, LIKE, EXISTS, IS NULL, and IS NOT NULL.

You can combine any two predicates with AND and OR. In addition, you can use the NOT keyword to specify that the search condition that you want is the negated value of the specified search condition. A WHERE clause can have as many predicates as you want.

• AND says that, for a row to qualify, the row must satisfy both predicates of the search condition. For example, to find out which employees in department D21 were hired after December 31, 1987, specify:

  …
    WHERE WORKDEPT = 'D21' AND HIREDATE > '1987-12-31'

• OR says that, for a row to qualify, the row can satisfy the condition set by either or both predicates of the search condition. For example, to find out which employees are in either department C01 or D11, you can specify :

  …
    WHERE WORKDEPT = 'C01' OR WORKDEPT = 'D11'

  You can also use IN to specify this request: WHERE WORKDEPT IN ('C01', 'D11').

• NOT says that, to qualify, a row must not meet the criteria set by the search condition or predicate that follows the NOT. For example, to find all employees in the department E11 except those with a job code equal to analyst, you can specify:

  …
    WHERE WORKDEPT = 'E11' AND NOT JOB = 'ANALYST'

When SQL evaluates search conditions that contain these connectors, it does so in a specific order. SQL first evaluates the NOT clauses, next evaluates the AND clauses, and then the OR clauses.

You can change the order of evaluation by using parentheses. The search conditions enclosed in parentheses are evaluated first. For example, to select all employees in departments E11 and E21 who have education levels greater than 12, you can specify:

…
  WHERE EDLEVEL > 12 AND
     (WORKDEPT = 'E11' OR WORKDEPT = 'E21')

The parentheses determine the meaning of the search condition. In this example, you want all rows that have a:

• WORKDEPT value of E11 or E21, and

• EDLEVEL value greater than 12

If you did not use parentheses:

…
  WHERE EDLEVEL > 12 AND WORKDEPT = 'E11'
    OR WORKDEPT = 'E21'

Your result is different. The selected rows are rows that have:

• WORKDEPT = E11 and EDLEVEL > 12, or

• WORKDEPT = E21, regardless of the EDLEVEL value

If you are combining multiple equal comparisons, you can write the predicate with the ANDs as shown in the following example:

…
  WHERE WORKDEPT = 'E11' AND EDLEVEL = 12 AND JOB = 'CLERK'

You can also compare two lists, for example:

…
  WHERE (WORKDEPT, EDLEVEL, JOB) = ('E11', 12, 'CLERK')

When two lists are used, the first item in the first list is compared to the first item in the second list, and so on through both lists. Thus, each list must contain the same number of entries. Using lists is identical to writing the query with AND. Lists can only be used with the equal and not equal comparison operators.

 

Parent topic:

Defining complex search conditions

 

Related reference

Specifying a search condition using the WHERE clause