Data Editor - Set Operations

If you mark in the top Table two records r1 and r2, then you get in the related Table two sets S1 and S2 of linked child-records (we consider 1:M case). This observation can move you to idea that we should be able do Set Operations on these sets. Exactly this allows you menu “Set Operations” with standard SET operations:

• AND (intersection). Operation outputs true when all inputs are true (one is true, the other is true)
• OR (union). Operation outputs true when at least one input is true (one is true, the other is false)
• XOR (exclusive OR). Operation outputs true only when inputs differ (one is true, the other is false)
• Difference (Minus). Operation outputs what is in one but not in the other (one is present, the other is not present)

To show the full power of this operations we will use database with two tables Person and Phone linked with the help of BinaryLink of M:M kind. Only Valentina database have such feature.

Picture shows how records are linked:

• The record [2] in the table Person linked with records [1, 3, 6] in the table Phone.
• The record [5] in the table Person linked with records [3, 4] in the table Phone.

Below will be described how the “set operations” affects the result.

The result of AND operation will be a set of child-records that are included into each set.

In our example, Persons 2 and 5 have common phone number with RecID = 3.

Look how complex enough looks minimal SQL command, which do similar job.

SELECT Phone.*
FROM Person JOIN Phone ON Person.ID = Phone.PersonPtr
WHERE Person.ID = 2
INTERSECTION
SELECT Phone.*
FROM Person JOIN Phone ON Person.ID = Phone.PersonPtr
WHERE Person.ID = 5

The result of OR operation will be a set of child-records that are included into at least one set.

In our example, Persons 2 and 5 use together Phones {1, 3, 6}.

Look how complex enough looks minimal SQL command, which do similar job.

SELECT Phone.*
FROM Person JOIN Phone ON Person.ID = Phone.PersonPtr
WHERE Person.ID = 2
UINION
SELECT Phone.*
FROM Person JOIN Phone ON Person.ID = Phone.PersonPtr
WHERE Person.ID = 5

The DIFFERENCE shows the set of child-records that belongs to the first parent record and does not belong to the second parent record.

For example, if we mark in the top browser, the fifth record as “1” and the second record as “2” then the difference contains record { 4 }.

Otherwise, if we mark the second record as “1” and the fifth record as “2” then the difference will contain records { 1, 6 }.

Look how complex enough looks minimal SQL command, which do similar job.

SELECT Phone.*
FROM Person JOIN Phone ON Person.ID = Phone.PersonPtr
WHERE Person.ID = 2
DIFFERENCE
SELECT Phone.*
FROM Person JOIN Phone ON Person.ID = Phone.PersonPtr
WHERE Person.ID = 5

The result of a XOR operation will be a set of child-records that belongs to some set, but are not common for all sets.

In our example the XOR operation for Persons 2 and 5 produces { 1, 6 } union { 4 } ⇒ { 1, 4, 6 }