Saturday, 1 June 2013

Inner Join, Outer Join and Union

Inner Join: Inner join is a way of combining two relations by matching a tuple of a relation with corresponding tuple in next relation, only when they have a common value in a particular attribute.

Outer Join: Outer join is a way of combining two relations by matching a tuple of a relation with corresponding tuple in next relation, but it also includes tuples which have no match in other relation. The tuple which exist on only one relation take null value in remaining attributes. An outer join is either a left-outer join or a right-outer join based on whether all items are taken from relation on left or relation on right respectively.

Union: Union is a way of combining two relations by taking all the elements of both relations.  

Take the following example:

Relation A:
Name
Age
Randy
16
Nathan
12
Sunny
15

Relation B:
Name
Grade
Randy
11
Nathan
7
Yogi
10
Rachel
8

Union of A and B:
Name
Age
Name
Grade
Randy
16
Randy
11
Nathan
12
Nathan
7
Sunny
15
Sunny
null
Yogi
null
Yogi
10
Rachel
null
Rachel
8
*union has all elements from both relations.

Inner Join:
Name
Age
Name
Grade
Randy
16
Randy
11
Nathan
12
Nathan
7
*inner join has only elements which can be matched from A to B.

Outer Join:
Name
Age
Name
Grade
Randy
16
Randy
11
Nathan
12
Nathan
7
Yogi
null
Yogi
10
Rachel
null
Rachel
8

*it has even elements which are not in A and this outer join is specifically a right outer join as it includes all items from relation B which we assume to be on the right.

No comments:

Post a Comment

Was this post helpful? Ask any questions you have, I will try to answer them for you.

Follow by Email