Common Elements › Where Clauses › IN Clause

IN Clause

The IN clause requires some special explanation. The two syntactic forms are:

SREL_attr_name.subq_WHERE_attr[.attr] IN ( value1 [, value2 [,…]] )
SREL_attr_name.[subq_SELECT_attr]LIST_name.subq_WHERE_attr IN (value1, [,value2 [,…]] )

The left side of the clause must begin with an SREL-type attribute of the table being queried, which is represented by SREL_attr_name. subq_WHERE_attr is an attribute of the foreign object, which itself may be another SREL pointer.

For example, a query against the request (‘cr’) object may be coded as follows:

category.sym IN (’Soft%’, ’Email’)

This translates to the following pseudo-SQL:

SELECT … FROM cr WHERE cr.category IN (SELECT persistent_id FROM pcat WHERE sym LIKE ‘Soft%’ OR sym = ‘Email’)

In the previous sub query , ‘pcat’ is the object name pointed to by cr.category.

The second form of the IN clause can search through BREL lists. For example, to find all requests assigned to an analyst in a specific group, the clause is as follows:

assignee.[member]group_list.group IN (U’913B485771E1B347968E530276916387’)

The first part of the clause, assignee, is an SREL (foreign key) of the cr object, pointing to the cnt object. Next, group_list, which is an attribute of the cnt object, is a list of cnt objects that represent groups to which a contact belongs. The last part, group, forms the first part of the where clause for the IN sub query. ‘U’913B485771E1B347968E530276916387is the foreign key value to match on group. The sub query return is specified by [member]. This translates to the following pseudo-SQL statement:

SELECT … FROM cr WHERE cr.assignee IN (SELECT member from grpmem WHERE group = U’913B485771E1B347968E530276916387’)

You can specify multiple foreign keys for matching multiple objects by providing a comma-separated list:

assignee.[member]group_list.group IN (U’913B485771E1B347968E530276916387’, U’913B485771E1B347968E530276916300’)

You cannot extend the dot notation for this use of the IN clause, for example, the following is not valid:

assignee.[member]group_list.group.last_name IN (‘Account Center’)

One use of IN is to avoid Cartesian products. For example, the following query results in a Cartesian product and is very inefficient:

assignee.last_name LIKE ’MIS%’ OR group.last_name LIKE ’MIS%’

Using IN, the query can be coded as follows:

assignee.last_name IN ’MIS%’ OR group.last_name IN ’MIS%’

This query does not create a Cartesian product; in fact, it creates no joins at all.

Note: The parentheses that normally enclose the list of values on the right side of IN can be omitted if there is only one value in the list. Similarly, you should avoid joins by converting queries, as shown by the following example:

assignee.last_name LIKE ‘Smith’

to:

assignee = U’913B485771E1B347968E530276916387’

This avoids the join with some loss in clarity. Using IN, the same partition can be written as follows, with the clarity of the first version and almost the same efficiency as the second version:

assignee.last_name IN ‘Smith’

The ‘NOT’ keyword cannot be in conjunction with IN, for example, “NOT IN”.