We can combine relations together in interesting ways.
Join (AND)
If two relations have one or more attributes in common, then they're talking about the same things.
This means we can join them using the natural join, & (or ⋈ if your keyboard can create it).
S & SP
| SNO | SNAME | STATUS | CITY | PNO | QTY |
|---|
| S1 | Smith | 20 | London | P1 | 300 |
| S1 | Smith | 20 | London | P2 | 200 |
| S1 | Smith | 20 | London | P3 | 400 |
| S1 | Smith | 20 | London | P4 | 200 |
| S1 | Smith | 20 | London | P5 | 100 |
| S1 | Smith | 20 | London | P6 | 100 |
| S2 | Jones | 10 | Paris | P1 | 300 |
| S2 | Jones | 10 | Paris | P2 | 400 |
| S3 | Blake | 30 | Paris | P2 | 200 |
| S4 | Clark | 20 | London | P2 | 200 |
| S4 | Clark | 20 | London | P4 | 300 |
| S4 | Clark | 20 | London | P5 | 400 |
Notice how we defined our relations with key attribute names specific to the type of relation, e.g. SNO and PNO rather than id for both.
This allows us to use the natural join, and other operators, to combine them sensibly with other relations.
It also helps with schema design and constraint definition.
This goes against many current naming conventions which do use id for every SQL table, to make life easier for the ORM systems.
We can still join relations if the attribute names don't initially match - just rename one of them, e.g. R{*, r_id:=id}
Cross Join (Times / Cartesian Product)
If two relations have no attributes in common, we can still join every tuple from one with every tuple from the other.
This is a cross join and is not so useful. We can use * (or × if your keyboard can create it).
S{SNO}
P{PNO}
S{SNO} * P{PNO}
| SNO | PNO |
|---|
| S1 | P1 |
| S1 | P2 |
| S1 | P3 |
| S1 | P4 |
| S1 | P5 |
| S1 | P6 |
| S2 | P1 |
| S2 | P2 |
| S2 | P3 |
| S2 | P4 |
| S2 | P5 |
| S2 | P6 |
| S3 | P1 |
| S3 | P2 |
| S3 | P3 |
| S3 | P4 |
| S3 | P5 |
| S3 | P6 |
| S4 | P1 |
| S4 | P2 |
| S4 | P3 |
| S4 | P4 |
| S4 | P5 |
| S4 | P6 |
| S5 | P1 |
| S5 | P2 |
| S5 | P3 |
| S5 | P4 |
| S5 | P5 |
| S5 | P6 |
Set operations
If two relations have all the same attributes, we can combine them in three ways:
- intersection
- union
- difference
Intersection
We can use & again (or ∩ if your keyboard can create it) to get the intersection.
S[STATUS>10]
| SNO | SNAME | STATUS | CITY |
|---|
| S1 | Smith | 20 | London |
| S3 | Blake | 30 | Paris |
| S4 | Clark | 20 | London |
| S5 | Adams | 30 | Athens |
S[STATUS<30]
| SNO | SNAME | STATUS | CITY |
|---|
| S1 | Smith | 20 | London |
| S2 | Jones | 10 | Paris |
| S4 | Clark | 20 | London |
S[STATUS>10] & S[STATUS<30]
| SNO | SNAME | STATUS | CITY |
|---|
| S1 | Smith | 20 | London |
| S4 | Clark | 20 | London |
Union (OR)
We can use | (or ∪ if your keyboard can create it) to get the union.
S[STATUS=10]
| SNO | SNAME | STATUS | CITY |
|---|
| S2 | Jones | 10 | Paris |
S[STATUS=30]
| SNO | SNAME | STATUS | CITY |
|---|
| S3 | Blake | 30 | Paris |
| S5 | Adams | 30 | Athens |
S[STATUS=10] | S[STATUS=30]
| SNO | SNAME | STATUS | CITY |
|---|
| S2 | Jones | 10 | Paris |
| S3 | Blake | 30 | Paris |
| S5 | Adams | 30 | Athens |
Difference (MINUS)
We can use - to get the difference.
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 |
S[STATUS=30]
| SNO | SNAME | STATUS | CITY |
|---|
| S3 | Blake | 30 | Paris |
| S5 | Adams | 30 | Athens |
S - S[STATUS=30]
| SNO | SNAME | STATUS | CITY |
|---|
| S1 | Smith | 20 | London |
| S2 | Jones | 10 | Paris |
| S4 | Clark | 20 | London |
Difference is not commutative: (R - S) != (S - R) in general.