Switch to: V9V8V7V6V5

Data Editor - Set Operations

Let's note, that 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)
  • OR (union)
  • XOR (exclusive OR)
  • Difference

Example

Records Relationship

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.

Pay attention to the order of selected records in the top browser - it is very important for some SET operations.

AND (intersection)

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

TIP: In simple terms, this operation allows to find common children of two or few parent records.

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

Operation - AND

NOTE: This operation has sense only for M:M link. Because for a 1:M link and, than more, for a 1:1 link, parent-records will have sets of child-records that do not intersect.

SQL Analog

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

OR (union)

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

TIP: In simple terms, this operations allows you to find all children of two or few parent records.

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

Operation - OR

SQL Analog

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

NOTE: This operation has sense for 1:1, 1:M and M:M links.

- (difference)

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

NOTE: This operation has sense only for M:M link. Because for a 1:M link and, than more, for a 1:1 link, parent-records will have sets of child-records that do not intersect.

SQL Analog

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

XOR

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

TIP: In simple terms, this operation means: show only child-records that belong to one and only parent.

TIP: S1 XOR S2 = (S1 - S2) UNION (S2 - S1)

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

Operation - XOR

NOTE: This operation makes sense only for M:M link. Because for a 1:M link and especially for a 1:1 link, parent-records will have sets of child-records that do not intersect.