Relational Algebra : Different types of Join Operations: Inner join, Outer join, Self join

JOIN is used to combine related tuples from two relations. In its simplest form the JOIN operator is just the cross product of the two relations. As the join becomes more complex, tuples are removed within the cross product to make the result of the join more meaningful. JOIN allows you to evaluate a join condition between the attributes of the relations on which the join is undertaken. The notation used is:



Inner Join

In inner join only those tuples from two relations are joined that have same value in the common attribute. For example, if we have two relations R and S with schemes R(a, b, c, d) and S(f, r, h, a), then we have 'a' as common attribute between these two relations. the inner join between these two table can be performed on the basis of 'a' which is common attribute between the two. The common attributes are not required to have the same name in both relations,  however, they must have the same domain in both relations. The attributes in both tables are generally tied in a primary-foreign key relationship but that also is not required. For example consider the following two relations class and class_info:



Now the inner join query between two relations will be :



the result relation will be :



Outer Join

Outer Join is based on both matched and unmatched data. The outer-join operation is an extension of the join operation to deal with missing information. An outer join retains the information that would have been lost from the tables or relations, replacing missing data with nulls. For example consider the two relations which contains employee and full time employee details :



now consider the employee and full_time_employee relations in the above figure. Suppose that we want to generate a single relation with all the information (street, city, branch name, and salary) about full-time employees. A possible approach would be to use the natural-join operation, but with the natural-join operation we lost the street and city information about Rick, since the tuple describing Rick is absent from the full_time_employee relation. Similarly, we have lost the branch name and salary information about James, since the tuple describing James is absent from the employee relation. We can use the outer-join operation to avoid this loss of information. There are three forms of the operations :

1. Left outer join :  The left outer join takes all tuples in the left relation that did not match with any tuple in the right relation, pads the tuples with null values for all other attributes from the right relation, and adds them to the result of the natural join. All information from the left relation is present in the result of the left outer join. 



The relation that results from the preceding query is will be : 



2. Right outer join : The right outer join is symmetric with the left outer join: It pads tuples from the left relation that did not match any from the right relation with nulls and adds them to the result of the natural-join. Thus, all information from the right relation is present in the result of the right outer join.  Thus, all information from the right relation is present in the result of the right outer join.



The relation that results from the preceding query is will be : 




3. Full outer join : The full outer join does both of those operations, padding tuples from the left relation that did not match any from the right relation, as well as tuples from the right relation that did not match any from the left relation, and adding them to the result of the join.



The relation that results from the preceding query is will be :



Self Join :

A table can also join to itself known as, Self Join. In self join a table is joined with itself. This operation is used when a table contains the reference of itself. In self-join the table has a FOREIGN KEY which references its own PRIMARY KEY. For example consider we have a table employee:



in the employee table displayed above, id is the primary key, employee_superviser is the foreign key which is nothing but the supervisers's employee id. Now if we want a list of employees and the names of their supervisers, we will have to join the employee table to itself to get the list.

Next Topic :-  

No comments:

Post a Comment