Introduction to database management systems

Join is a combination of a Cartesian product with a selection process. A Join operation pairs two tuples from different relations, if and only if a join conditions are satisfied.

Theta (θ) Join

Theta join combines tuples from relations provided they satisfy theta condition. The join condition is denoted by symbol θ.

X1 ⋈θ X2

X1 and X2 are relations having attributes (A1, A2, .., An) and (B1, B2,.. ,Bn) such that attributes don’t have anything in common, that is X1 ∩ X2 = Φ.

Equi Join

SQL EQUI JOIN performs a JOIN against equality or matching column(s) values of associated tables. An equal sign (=) is used as comparison operator in where clause to refer equality.

SELECT column_list FROM table1, table2....WHERE table1.column_name =table2.column_name;

Natural Join (⋈)

Natural join do not use any comparison operator. It do not concatenate way a Cartesian product does.

SELECT * FROM table1 NATURAL JOIN table2;

The associated tables have one or more pairs of identically named columns and same data type.

Outer Joins

Equi join, Theta Join and Natural Join are part of inner joins. An inner join includes only those tuples with matching attributes and rest are discarded in resulting relation. We use outer joins to include all tuples from participating relations in resulting relation.

 There are three kinds of outer joins − right outer join, left outer join and full outer join.

Left Outer Join (R Left Outer Join S)

All tuples from Left relation R are included in resulting relation. If there are tuples in R without any matching tuple in Right relation S, then S-attributes of resulting relation are made NULL.

Right Outer Join (R Right Outer Join S)

All tuples from Right relation S are included in resulting relation. If there are tuples in S without any matching tuple in R, then R-attributes of resulting relation are made NULL.

Full Outer Join (R Full Outer Join S)

All tuples from both participating relations are included in resulting relation. If there are no matching tuples for both relations, their respective unmatched attributes are made NULL.