We can further combine relations.
Matching (semijoin)
To check which tuples from one relation have tuples with common attribute(s) present in another we can use &~ (or ⋉ if your keyboard can create it) to match them.
S| SNO | SNAME | STATUS | CITY |
|---|---|---|---|
| S1 | Smith | 20 | London |
| S2 | Jones | 10 | Paris |
| S3 | Blake | 30 | Paris |
| S4 | Clark | 20 | London |
| S5 | Adams | 30 | Athens |
SP| SNO | PNO | QTY |
|---|---|---|
| S1 | P1 | 300 |
| S1 | P2 | 200 |
| S1 | P3 | 400 |
| S1 | P4 | 200 |
| S1 | P5 | 100 |
| S1 | P6 | 100 |
| S2 | P1 | 300 |
| S2 | P2 | 400 |
| S3 | P2 | 200 |
| S4 | P2 | 200 |
| S4 | P4 | 300 |
| S4 | P5 | 400 |
S &~ SP| SNO | SNAME | STATUS | CITY |
|---|---|---|---|
| S1 | Smith | 20 | London |
| S2 | Jones | 10 | Paris |
| S3 | Blake | 30 | Paris |
| S4 | Clark | 20 | London |
This is similar to the often-used WHERE EXISTS (subselect) in SQL, e.g.
SELECT DISTINCT * FROM S WHERE EXISTS (SELECT 1 FROM SP WHERE SP.SNO = S.SNO)
Not Matching (semiminus / antijoin)
We can find ones not present using -&~ (or ▷ if your keyboard can create it).
S -&~ SP| SNO | SNAME | STATUS | CITY |
|---|---|---|---|
| S5 | Adams | 30 | Athens |
This is similar to the often-used WHERE NOT EXISTS (subselect) in SQL.
Matching and Not Matching are not commutative, but they do have reverse counterparts: ~& and ~&- (or ⋊ and ◁).