Search Condition Using IN

The IN operator is used when you want to select rows that match one of several listed values. If the row value matches any value in the list of values, the row is selected.

The format of this operator is:

     expression [NOT] IN (value1, value2, value3 ...)

Value1, value2, and so on indicates a list of values. Enclose the entire list in parentheses. Separate items in the list by commas.

Example: Let's list all departments in either the Corporate, Product, or Information divisions.

Enter the statement:

     SELECT  DNAME, DIV
     FROM    DEPARTMENT
     WHERE   DIV IN ('CORP', 'PROD', 'INFO') ;

The result displayed will be:

DNAME DIV Management and Administration CORP Finance and Accounting CORP Corporate Legal Support CORP Supplies and Procurement CORP Recruitment and Personnel CORP Research and Development PROD Manufacturing PROD Customer Support and Service PROD Product Marketing and Sales PROD Information Systems Management INFO Corporate Library INFO Strategic Planning and Intelligence INFO

An equivalent query can be formed using the logical operator OR. It looks like the following:

     SELECT  DNAME, DIV
     FROM    DEPARTMENT
     WHERE   DIV = 'CORP'  OR  DIV = 'PROD'  OR  DIV = 'INFO' ;

The IN operator can be modified with the logical operator NOT.

Example: List all departments which are not in either the Corporate or the Information divisions.

Enter the statement:

     SELECT  DNAME, DIV
     FROM    DEPARTMENT
     WHERE   DIV NOT IN ('CORP','INFO') ;

  DNAME                                DIV
  Research and Development             PROD
  Manufacturing                        PROD
  Customer Support and Service         PROD
  Product Marketing and Sales          PROD