INTERSECTION function

The INTERSECTION row function takes two sets of the same data type and returns a set that is the intersection of both input sets.

The syntax of the INTERSECTION function is: 
INTERSECTION(<set1>, <set2>)
where:
  • set1 is a set of any set data type (such as mdex:string-set). For example, set1 can be a multi-assign string attribute.
  • set2 is a set of the same set data type as set1. For example, if set1 is a multi-assign string attribute, then set2 must also be a set of strings (such as another multi-assign string attribute).
If an attempt is made to intersect two sets of different set data types, an error message is returned similar to this example: 
The function "INTERSECTION" is defined for the argument type(s) mdex:string-set, mdex:double-set

In this error case, INTERSECTION was used with a multi-assign string attribute (mdex:string-set) and a multi-assign double attribute (mdex:double-set) as inputs.

INTERSECTION example

In this example, both Body and Flavors are multi-assign string attributes and WineID is the primary key of the records: 
RETURN results AS
SELECT 
   WineID AS idRec,
   INTERSECTION(Body, Flavors) AS intersectAttrs
FROM WineState
WHERE WineID BETWEEN 5 AND 9
ORDER BY idRec
The result of this statement might be: 
idRec    intersectAttrs
-----------------------------
| 5 | { Earthy }            |
| 6 | { }                   |
| 7 | { }                   |
| 8 | { Oak }               |
| 9 | { Fruit, Strawberry } |
-----------------------------

Records 5 and 8 have one-element result sets because there is one intersection between their Body and Flavors assignments, while Record 9 has a two-element intersection. Records 6 and 7 return empty sets because there is no intersection among their Body and Flavors assignments.