Multiple conditions

You can use the AND and OR operators to combine predicates and search for data, based on multiple conditions.

Use the AND operator to specify that a search must satisfy both of the conditions. Use the OR operator to specify that the search must satisfy at least one of the conditions.

Examples

Begin general-use programming interface information.
Example
This query retrieves the employee number, hire date, and salary for each employee who was hired before 1998 and earns a salary of less than $35,000 per year: 
SELECT EMPNO, HIREDATE, SALARY
  FROM EMP
  WHERE HIREDATE < '1998-01-01' AND SALARY < 35000;
Example
This query retrieves the employee number, hire date, and salary for each employee who either was hired before 1998, or earns a salary less than $35,000 per year or both : 
SELECT EMPNO, HIREDATE, SALARY
  FROM EMP
  WHERE HIREDATE < '1998-01-01' OR SALARY < 35000;
End general-use programming interface information.

Examples: How to use parentheses with AND and OR

If you use more than two conditions with the AND or OR operators, you can use parentheses to specify the order in which you want Db2 to evaluate the search conditions. If you move the parentheses, the meaning of the WHERE clause can change significantly.

Begin general-use programming interface information.
Example
This query retrieves the row of each employee that satisfies at least one of the following conditions:
  • The employee's hire date is before 1998 and salary is less than $40,000.
  • The employee's education level is less than 18.
SELECT EMPNO
  FROM EMP
  WHERE (HIREDATE < '1998-01-01' AND SALARY < 40000) OR (EDL < 18);
Example
This query retrieves the row of each employee that satisfies both of the following conditions:
  • The employee's hire date is before 1998.
  • The employee's salary is less than $40,000 or the employee's education level is less than 18.
SELECT EMPNO
  FROM EMP
  WHERE HIREDATE < '1998-01-01' AND (SALARY < 40000 OR EDL < 18);
Example
This query retrieves the employee number of each employee that satisfies one of the following conditions:
  • Hired before 1998 and salary is less than $40,000.
  • Hired after January 1, 1998, and salary is greater than $40,000.
SELECT EMPNO
  FROM EMP
  WHERE (HIREDATE < '1998-01-01' AND SALARY < 40000)
  OR (HIREDATE> '1998-01-01' AND SALARY> 40000);
End general-use programming interface information.

Examples: How to use NOT with AND and OR

When you use NOT with AND and OR, the placement of the parentheses is important.

Begin general-use programming interface information.
Example
The following query retrieves the employee number, education level, and job title of each employee who satisfies both of the following conditions:
  • The employee's salary is less than $50,000.
  • The employee's education level is less than 18.
SELECT EMPNO, EDL, JOB
  FROM EMP
  WHERE NOT (SALARY>= 50000) AND (EDL < 18);

In this query, the NOT operator affects only the first search condition (SALARY>= 50000).

Example
The following query retrieves the employee number, education level, and job title of each employee who satisfies at least one of the following conditions:
  • The employee's salary is less than or equal to $50,000.
  • The employee's education level is less than or equal to 18.
SELECT EMPNO, EDL, JOB
  FROM EMP
  WHERE NOT (SALARY> 50000 AND EDL> 18);

To negate a set of predicates, enclose the entire set in parentheses and precede the set with the NOT keyword.

End general-use programming interface information.