Retrieving Information from a Table › Retrieving Selected Rows › Comparisons to a list of values

Comparisons to a list of values

Using IN

To compare a value to one of several values in a list, use the IN keyword. The IN keyword is a short way of coding two or more comparison expressions connected by an OR. For example, both of these SELECT statements yield the same result:

select empid
  from emp
  where deptid in (3100, 4000)

select empid
  from emp
  where (deptid = 3100) or
    (deptid = 4000)

Separate each value in the list by a comma. A blank following the comma is optional.

Example

List all employees whose job falls into one of these classes: 11, 21, 43, or 71 (the report shown below indicates that there are no employees that have a job in class 11):

select firstname, lastname, class
 from emp
 where class in (11, 21, 43, 71)
 order by class ! display

                                   EMP REPORT
                                    mm/dd/yy

             FIRSTNAME                LASTNAME                CLASS
             ──────────            ───────────────            ─────
             RALPH                 TYRO                          21
             MICHAEL               ANGELO                        21
             CAROL                 MCDOUGALL                     21
             JULIE                 JENSEN                        43
             LARRY                 LITERATA                      43
             JENNIFER              GARFIELD                      71
     END OF REPORT