Relational Algebra

Basic Operators

Relational Algebra is a procedural query language which takes a relation as an input and generates a relation as an output.

σ(Selection) - Select rows based on given condition.
∏(Projection) - Project some columns.
X (Cross Product) - Cross product of relations, returns m*n rows where m and n are number of rows in R1 and R2 respectively.
U (Union) - Return those tuples which are either in R1 or in R2. Max no. of rows returned = m+n and Min no. of rows returned = max(m,n).
−(Minus) - R1-R2 returns those tuples which are in R1 but not in R2. Max no. of rows returned = m and Min no. of rows returned = m-n.
ρ(Rename) - Renaming a relation to another relation.

Extended operators provide more complex operations on relations:

∩ (Intersection) - Returns those tuples which are in both R1 and R2. Max no. of rows returned = min(m,n) and Min no. of rows returned = 0.
⋈c(Conditional Join) - Selection from two or more tables based on some condition (Cross product followed by selection).
⋈(Equi Join) - It is a special case of conditional join when only equality conditions are applied between attributes.
⋈(Natural Join) - In natural join, equality conditions on common attributes hold and duplicate attributes are removed by default. Note: Natural Join is equivalent to cross product if two relations have no attribute in common and natural join of a relation R with itself will return R only.
⟕(Left Outer Join) - Gives all tuples of R in the result set. The tuples of R which do not satisfy the join condition will have values as NULL for attributes of S.
⟖(Right Outer Join) - Gives all tuples of S in the result set. The tuples of S which do not satisfy the join condition will have values as NULL for attributes of R.
⟗(Full Outer Join) - Gives all tuples of S and all tuples of R in the result set. The tuples which do not satisfy the join condition will have values as NULL for attributes of the other relation.
/(Division Operator) - Division operator A/B will return those tuples in A which are associated with every tuple of B. Note: Attributes of B should be a proper subset of attributes of A.