Join and types of Joins
JOIN allows information to be combined from two or more tables. JOIN is the real power behind the relational database, allowing the use of independent tables linked by common attributes. The CUSTOMER and AGENT tables shown in the following Figure will be used to illustrate several types of joins.
Natuaral Join:
A natural join links tables by selecting only the rows with common values in their common attribute(s). A natural join is the result of a three-stage process:
a. First, a PRODUCT of the tables is created, yielding the results shown in the following Figure .
b. Second, a SELECT is performed on the output of Step a to yield only the rows for which the AGENT_CODE values are equal. The common columns are referred to as the join columns. Step b yields the results shown in the following Figure.
c. A PROJECT is performed on the results of Step b to yield a single copy of each attribute, thereby eliminating duplicate columns. Step c yields the output shown in Figure 3.14. The final outcome of a natural join yields a table that does not include unmatched pairs and provides only the copies of the matches.
Equi Join:
Another form of join, known as equijoin, links tables on the basis of an equality condition that compares specified columns of each table. The outcome of the equijoin does not eliminate duplicate columns, and the condition or criterion used to join the tables must be explicitly defined. The equijoin takes its name from the equality comparison operator (=) used in the condition. If any other comparison operator is used, the join is called a theta join.
Each of the preceding joins is often classified as an inner join.
An inner join is a join that only returns matched records from the tables that are being joined.
In an outer join, the matched pairs would be retained, and any unmatched values in the other table would be left null.
It is an easy mistake to think that an outer join is the opposite of an inner join. The outer join still returns all of the matched records that the inner join returns, plus it returns the unmatched records from one of the tables. More specifically, if an outer join is produced for tables CUSTOMER and AGENT, two scenarios are possible:
Left Outer Join:
A left outer join yields all of the rows in the CUSTOMER table, including those that do not have a matching value in the AGENT table. An example of such a join is shown in the following figure.
Right Outer Join:
A right outer join yields all of the rows in the AGENT table, including those that do not have matching values in the CUSTOMER table. An example of such a join is shown in the following Figure. Generally speaking, outer joins operate like equijoins. The outer join does not drop one copy of the common attribute, and it requires the specification of the join condition.
In the above Figures illustrate the result of outer joins after a relational PROJECT operation is applied to them to manually remove the duplicate column. Outer joins are especially useful when you are trying to determine what value(s) in related tables cause(s) referential integrity problems.
You May Also Like:
Codd’s Relational Database Rules
