Relational Algebra
Relational Algebra |
English |
Explanation |
SQL |
Example |
πL(R) |
Projection of R on L |
Retrieves only column L from table R, removing duplicates. |
SELECT DISTINCT L FROM R;
|
If R has columns (ID, Name, Age) and data {(1, Alice, 25), (2, Bob, 30)}, then πName(R) returns {Alice, Bob}. |
σC(R) |
Selection (Filter) of R by condition C |
Retrieves rows that satisfy condition C. |
SELECT DISTINCT * FROM R WHERE C;
|
If R has {(1, Alice, 25), (2, Bob, 30)} and C is Age > 25, then σage>25(R) returns {(2, Bob, 30)}. |
ρS(R) |
Renaming R as S |
Renames relation R to S for easier reference. |
|
If R contains {(1, Alice), (2, Bob)}, then ρT(R) treats it as T with the same data. |
R ⨝ S |
Natural Join of R and S |
Joins R and S on common attributes. |
R NATURAL JOIN S
(or, as SELECT) SELECT DISTINCT * from R NATURAL JOIN S;
|
If R = {(1, Alice), (2, Bob)} and S = {(1, NYC), (2, LA)}, R ⨝ S = {(1, Alice, NYC), (2, Bob, LA)}. |
R ⨝θ S |
Theta Join of R and S on condition θ |
Joins R and S where θ holds. |
R INNER JOIN S
SELECT DISTINCT * from R INNER JOIN S ON θ;
SELECT DISTINCT * FROM R, S WHERE θ
|
If R has {(1, Alice), (2, Bob)} and S has {(1, NYC), (3, LA)}, then with θ: R.ID = S.ID, the result is {(1, Alice, NYC)}. |
R ⊗ S |
Cartesian Product of R and S |
Pairs every row of R with every row of S. |
SELECT DISTINCT * FROM R, S;
|
If R = {(1, Alice)} and S = {(NYC), (LA)}, then R ⊗ S = {(1, Alice, NYC), (1, Alice, LA)}. |
|
