Values in a list

You can use the IN predicate to select each row that has a column value that is equal to one of several listed values.

In the values list after the IN predicate, the order of the items is not important and does not affect the ordering of the result. Enclose the entire list of values in parentheses, and separate items by commas; the blanks are optional.

Examples

Begin general-use programming interface information.

Example

The following query retrieves the department number and manager number for departments B01, C01, and D11:

SELECT DEPTNO, MGRNO
  FROM DEPT
  WHERE DEPTNO IN ('B01', 'C01', 'D11');

Using the IN predicate gives the same results as a much longer set of conditions that are separated by the OR keyword.

Example

You can alternatively code the WHERE clause in the SELECT statement in the previous example as:

WHERE DEPTNO = 'B01' OR DEPTNO = 'C01' OR DEPTNO = 'D11';

However, the IN predicate saves coding time and is easier to understand.

Example

The following query finds the projects that do not include employees in department C01 or E21:

SELECT PROJNO, PROJNAME, RESPEMP 
  FROM PROJ                               
  WHERE DEPTNO NOT IN ('C01', 'E21');

End general-use programming interface information.