DIFFERENCE function

The syntax of the DIFFERENCE function is:

DIFFERENCE(<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).

Examples of some results are as follows ({ } indicates an empty set):

DIFFERENCE({ 1, 2, 3, 4, 5 }, { 1, 3, 5 }) = { 2, 4 }
DIFFERENCE({ }, { 1, 3, 5 }) = { }
DIFFERENCE({ 1, 2, 3 }, { }) = { 1, 2, 3 }
DIFFERENCE({ 1, 2 }, { 'a', 'b' }) yields a checking error because the two sets are not of the same data type

In the examples below, both Body and Flavors are multi-assign string attributes. Their values for five records are:

Record 5: Body=Earthy, Silky, Tannins
          Flavors=Blackberry, Earthy, Toast
Record 6: Body=Robust
          Flavors=Berry, Plum, Zesty
Record 7: Body=Silky, Tannins
          Flavors=Cherry, Pepper, Prune
Record 8: Body=Oak, Robust
          Flavors=Cherry, Oak, Raspberry
Record 9: Body=Fruit, Strawberry, Silky, Tannins
          Flavors=Fruit, Earthy, Strawberry

First, we want all the elements of the Body set that do not appear in the Flavors set:

RETURN results AS
SELECT 
   WineID AS idRec,
   DIFFERENCE(Body, Flavors) AS diffAttrs
FROM WineState
WHERE WineID BETWEEN 5 AND 9
ORDER BY idRec

The result of this statement might be:

diffAttrs              idRec
----------------------------
| { Silky, Tannins }   | 5 |
| { Robust }           | 6 |
| { Silky, Tannins }   | 7 |
| { Robust }           | 8 |
| { Silky, Tannins }   | 9 |
----------------------------

We then reverse the difference comparison between the two sets. The statement is identical to the first example, except that Flavors is the first argument rather than Body:

RETURN results AS
SELECT 
   WineID AS idRec,
   DIFFERENCE(Flavors, Body) AS diffAttrs
FROM WineState
WHERE WineID BETWEEN 5 AND 9
ORDER BY idRec

This time, the result of this statement will look different:

diffAttrs                     idRec
-----------------------------------
| { Blackberry, Toast }       | 5 |
| { Berry, Plum, Zesty }      | 6 |
| { Cherry, Pepper, Prune }   | 7 |
| { Cherry, Raspberry }       | 8 |
| { Earthy }                  | 9 |
-----------------------------------