Notes and Study Materials

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive
 

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.

Join and 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 .

Join and types of Joins_natural join

 

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.

 

 

Join and types of Joins_natural join columns

 

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.

Join and types of Joins_ Project

 

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.

 

Join and types of Joins_left outer join

 

 

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.

 

Join and types of Joins.Right outer Join

 

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:

Characterstics of Table

Types of Keys in DBMS 

Integrity Rules in DBMS

Relational Set Operaotrs

Codd’s Relational Database Rules

Back To DBMS Questions