Joining tables in databases
Joins in DBMS
Join is a binary operation and a specialization of cross product between two tables. Join is an operation that combines cross product and selection in one single statement. There are a number of different joins in DBMS:
- Theta Join
- Equi Join
- Natural Join
- Outer Joins
Theta joins merges the two table on the basis of the condition represented by theta (θ) and is denoted by R1 ⋈θ R2.First it does the cross product and then select the rows on the basis of given condition. Theta joins work for all comparison operators.
In equijoin, tables are merged on the basis of common attribute between them. When equality comparison operator (=) is used in Theta join, then it becomes Equi-Join.
No comparison operator is used in Natural join. This join is much different then Cross product. Unlike cross product, it does not keep all the attributes of both relations. Actually, natural join can be applied only if both relations have at least one common attribute with same name and domain. Natural join merge the tables on the basis of that common attributes where value for that attribute is same in both relation. Whereas cross product ,theta join and equijoin all has all attributes of merging tables and therefore having redundancy also. But natural joins keeps only one column for common attribute in the resulting relation.
All the above given joins are also known as inner-joins. Inner join keeps only the tuples satisfying the given condition or matching attribute but in outer join, all the tuples of a relation are present in the resulting relation based on the type of join.
Left outer join (R ⟕ S)
In this join, resulting relation keeps all the tuples of the left relation i.e R and For the tuples of R having no matching tuple in S, the attributes of S are made NULL in the resulting relation.
Right outer join ( R ⟖ S)
In this join, resulting relation keeps all the tuples of the Right relation i.e S and For the tuples of S having no matching tuple in R, the attributes of R are made NULL in the resulting relation.
Full outer join (R ⟗ S)
Full outer join actually combines the result of both left outer join and right outer join. Resulting relation has all tuples of both relations and for non-matching tuples in both relations, their respective attributes of other relation are made NULL.