JOIN Relational Operation

Join is a special form of cross product of two tables. It is a binary operation that allows combining certain selections and a Cartesian product into one operation.

Types of Joins
a) Theta Join
   -  Apply condition on input relation (s) and then only those selected rows are used in the cross product to be merged and included in output.
 -    Differs from normal cross product in the sense that all rows are merged in the case normal cross product and only selected rows in case of theta join
 -   E.g. Cross product of COURSE with FACULTY of the rank of asso prof

b) Equi-Join
  -  Most common type of join where rows are joined on the basis of values of a common attribute between the two relations
- usually the case of primary and foreign key
- common attribute name will appear twice in output relation
- it's better to qualify the name of common attribute by prefixing relation name to it
- E.g. All books issued to a particular student. Student Id is a foreign key BOOK relation

c) Natural Join
- Join, in general, means normal join
- same as Equi-join except that in natural join common attribute appears only once
- any common attribute can be taken in output relation

d) Outer Join: Type of natural joins with following types and characteristics
i) Left Outer Join: Equi join + non-matching rows of left side relation having NULL against the attributes of right side relation
E.g. BOOK issued and not issued
ii) Right Outer Join: will always have all tuples of right relation and those tuples of left relation which are not matched are left as NULL
E.g. Students who have not borrowed any book
iii) Outer Join: all the tuples of left and right relations are part of output relation with NULL in non-matching tuples of both left and right side relations
E.g. Books  issued/not issued and students who have not borrowed any book

e) Semi Join:
Take the natural join of two relations and project attributes of first relation only