DIFFERENCE function

The DIFFERENCE row function takes two sets of the same data type and returns a set containing all of the elements of the first set that do not appear in the second set.

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

DIFFERENCE example

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
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 |
----------------------------

Records 5, 7, and 9 have "Silky" and "Tannins" in the Body set, but these values do not appear in the Flavors set. Likewise, Records 6 and 8 have "Robust" in the Body set, but that value does not appear in the Flavors set.

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
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 |
-----------------------------------

To take Record 9 as an example of the output, "Earthy" is the only element from the first set (the Flavors set) that does not appear in the second set (the Body set).