Searches Using IN

Searches Using `IN`

The IN operator is used to select rows that match one of several listed values. In Texis version 7 and later it behaves similar to the SUBSET operator (here), i.e. it is true if all left-side value(s) are also present on the right-side. (See below for version 6 and earlier differences.)

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 row is retrieved if a department's division is in the set of divisions.

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

A semantically equivalent (but usually less efficient) 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 right-side of the IN operator may also be a strlst table column, in which case for each row, the left-side value is compared against each individual strlst item for that row. Parentheses are not needed in this case:

SELECT UserName
     FROM   Users
     WHERE  'Administrator' IN GroupMembership;

In the above example, the GroupMembership column is of type strlst, and contains the list of groups that each user (row) is a member of. The query will thus return all UserNames that are members of the "Administrator" group.

The left-side of an IN operator may also be multi-value (e.g. a strlst parameter), in which case all the left-side values must be present on the right-side (if inmode is "subset"). The behavior of multi-value types other than strlst (on either side of IN) is currently undefined and thus such types should not be used.

The IN operator can be modified with the logical operator NOT (note however that an index cannot be used to optimize such a query).

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') ;

The result displayed will be:

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

Note that IN differs from SUBSET and INTERSECT in the interpretation of empty varchar values: for IN they are single-item empty-string sets. See here for details, as well as for other behaviors that IN, SUBSET and INTERSECT share in common.

`IN` with Version 6 Or Earlier

In Texis version 6 (or compatibilityversion 6) and earlier, IN behaved much like the INTERSECT operator (here) instead of SUBSET, i.e. it was true if any left-side value was present on the right-side. This behavior can be restored with the inmode SQL property, here (or the compatibilityversion property, here). Note however that with a single non-empty left-side value, there is no difference, as intersection and subset then behave the same.

Additionally, IN with version 6 and earlier did not always utilize indexes (e.g. if the table column was on the right-side), and had other quirks.