The SUBSET operator allows subset queries, and is typically
used with multi-value (i.e.
strlst) fields that are treated as
sets. It is true if the left-side is a subset of the right-side,
i.e. if there are no values on the left-side that are missing from the
right-side. Duplicates count, i.e. they must match one-to-one from
left side to right.
For example, suppose the table
Users contains one row per
UserName), and has a
GroupMembership that lists all the groups that row's user is a
member of. To find all users that are members of groups
Marketing", a SUBSET query can be used:
WHERE ('Management', 'Sales', 'Marketing')
IS SUBSET OF GroupMembership;
(Syntactically, SUBSET is always used as part of the phrase IS SUBSET OF, as it is only valid in WHERE clauses.) The above query will return the users that are members of all three groups - including any users that may also be members of additional groups.
Note that SUBSET is not commutative, i.e. if the left- and
right-sides are reversed, the meaning is changed (unlike e.g. INTERSECT). If
A is a subset of
not necessarily a subset of
B is a subset of
A if and only if both sets contain the same values. E.g. this
IS SUBSET OF ('Management', 'Sales', 'Marketing');
while merely the reversed version of the earlier query, behaves differently: it would list the users whose are in zero or more of the Management, Sales or Marketing groups - and are not in any other groups.
In set logic the empty set is a subset of any set; thus if there are no values on the left-side, SUBSET is true no matter what the right-side value(s) are. Note that SUBSET interprets an empty varchar value as empty-set, not single-item empty-string set set (as IN does). See here for details, as well as additional behaviors that IN, SUBSET and INTERSECT share in common. SUBSET was added in Texis version 7.
A SUBSET query can often utilize a regular (B-tree) index to
increase performance. Generally the index should be created with
indexvalues set to
splitstrlst (the default), as this
enables individual values of
strlsts to be accessed as needed.
There are some limitations and caveats for SUBSET and indexes
strlstcolumn (either side): Queries with empty-set parameters (i.e. zero-item
strlst, or empty
varchar) and a
strlstcolumn cannot use an
indexvalues=splitstrlstindex, regardless of which side of SUBSET the parameter and column are on. An index with
indexvalues=allcan be used however. It may be created in addition to the normal
indexvalues=splitstrlstindex, and the Texis optimizer will choose the appropriate one at search time.
strlstcolumn left-side, non-empty parameter right-side: With a
strlstcolumn on the left-side, and a non-empty parameter on the right, empty rows will not be returned if an index is used - even though they properly match (as empty set is a subset of any set).
These caveats are due to limitations in
indexes; see here for more information.