IN

You can retrieve data from each row whose column, named in the WHERE clause, has a value equal to one of several listed values using OR. When applying search conditions to a column, sometimes it is easier to use the IN statement instead of multiple OR statements. When IN is used, at least two values must be specified within the parentheses. Enclose the list of values (excluding NULL, which cannot be used with IN) in parentheses. Separate one value from the next with a comma; a blank between values is optional.

The order of the objects in the list is not important; you receive the same rows in any case. The order of objects in the list does not affect the ordering of the result. To order the result, use ORDER BY.

This query:

SELECT DEPTNUMB, DEPTNAME
FROM Q.ORG
WHERE DEPTNUMB IN (20, 38, 42)

Produces this report:

DEPTNUMB DEPTNAME
-------- ---------------
      20  MID ATLANTIC
      38  SOUTH ATLANTIC
      42  GREAT LAKES

In the query above, IN(20, 38, 42) is equivalent to (DEPTNUMB = 20 OR DEPTNUMB = 38 OR DEPTNUMB = 42).

Examples:

• Select every department in the Eastern and Midwestern divisions:

  SELECT DEPTNAME, DIVISION, LOCATION
  FROM Q.ORG
  WHERE DIVISION IN ('EASTERN', 'MIDWEST')

• Select every salesperson and clerk in departments 15, 20, and 38:

  SELECT ID, NAME, JOB, DEPT
  FROM Q.STAFF
  WHERE JOB IN ('CLERK', 'SALES')
  AND DEPT IN (15, 20, 38)

• Select everyone with 1, 2, or 3 years of service, or whose years value is null:

  SELECT ID, NAME, YEARS
  FROM Q.STAFF
  WHERE YEARS IN (1, 2, 3) OR YEARS IS NULL