Searches Using INTERSECT

The INTERSECT operator allows set-intersection queries, typically on multi-value (i.e. strlst) values. It returns the intersection of the left and right sides, i.e. the "set" (strlst) of all values that are present on both sides. Duplicates are significant, i.e. they must match one-to-one to be included in the intersection.

For example, suppose the table Users contains one row per user (UserName), and has a strlst column GroupMembership that lists all the groups that row's user is a member of. To find all users that are members of groups "Management", "Sales" or "Marketing", an INTERSECT query can be used:

SELECT UserName
     FROM   Users
     WHERE  GroupMembership INTERSECT
          ('Management', 'Sales', 'Marketing') IS NOT EMPTY;

This will return users where the intersection of a user's GroupMembership with the three named groups is not empty (i.e. contains at least one value). Thus, users that are members of any of the three named groups are returned. The phrase IS NOT EMPTY must be added immediately after, both to turn the expression into a true/false condition suitable for a WHERE clause, and to allow an index to be used to resolve the query. (The phrase IS EMPTY is also permitted, for negation. However indexes cannot be used to resolve such queries.)

INTERSECT may also be used in a SELECT clause, to return the actual intersection set itself, rather than be used as a true/false condition. For example, given the same Users table above, to find each user's membership amongst just the three named groups, this query may be used:

SELECT UserName, GroupMembership INTERSECT
          ('Management', 'Sales', 'Marketing') AS SubMembership
     FROM   Users;

This will return the membership of each user (SubMembership) in just the three named groups, as a strlst. If a user is not a member of any of the three groups, SubMembership will be empty. If a user is a member of some other group(s), they will not be named in SubMembership.

Note that unlike SUBSET, INTERSECT is commutative, i.e. reversing the left- and right-sides does not change its meaning. (The "=" equals operator is also commutative, for example: x = y has the same meaning as y = x.) Also note that INTERSECT interprets an empty varchar value as empty-set, not single-item empty-string set (as IN does). See here for details, as well as additional behaviors that IN, SUBSET and INTERSECT share in common. INTERSECT was added in Texis version 7.

Index Usage by INTERSECT

An INTERSECT query can utilize a regular (B-tree) index to increase performance. The index should be created with indexvalues set to splitstrlst (the default), as this enables individual values of strlsts to be accessed as needed.