Join Operations in DBMS

Join Operations

Related tuples are combined from different relations in join operations. This holds valid only if a given join condition is met. Join operations are denoted by ⋈.

Example

EMPLOYEE

Emp_Code Emp _Name
101 Jai
102 Mahesh
103 Vishal

 

SALARY

Emp_Code Emp _Name
101 90000
102 130000
103 125000
 

Operation: (EMPLOYEE⋈SALARY)

RESULT:

Emp_Code Emp_Name Salary
101 Jai 90000
102 Mahesh 130000
103 Vishal 125000

 

Different Types of Join Operations

 

Natural Join

The set of tuples of all combinations of R and S that are equal on their common attribute names is called a natural join. Natural Join is denoted by ⋈.

Example

We shall use the employee salary table illustrated above in this example.

 

Operation:

ITEMP_NAME,SALARY (EMPLOYEE⋈ SALARY)

 

OUTPUT

Emp_Name Salary
Jai 90000
Mahesh 130000
Vishal 125000

 

 

Outer Join

The extension of the join operation is called Outer Join. Missing information is dealt with in this join.

Example

EMPLYEE

Emp_Name Street City
Jai Civil Line Mumbai
Bkadam Park Strrt Kolkata
Vivek M.G. Street Delhi
Asmita Nehru Nagar Hyderabad

 

FACT_WORKERS

Emp_Name Branch Salary
Jai B1 20000
Bkadam B2 30000
Dubey B4 40000
Asmita B3 60000

 

Operation:

(EMPLOYEE ⋈ FACT_WORKERS)

 

OUTPUT:

Emp_Name Street City Branch Salary
Jai Civil Line Mumbai B1 20000
Bkadam Park Street Kolkata B2 30000
Asmita Nehru Nagar Hyderabad B3 60000

 

There are three types of Outer Join

  1. Left Outer Join
  2. Right Outer Join
  3. Full Outer Join

 

Left Outer Join

  • Left outer join includes the set of tuples of all permutation in R and S that are identical on their common attribute names.
  • Tuples in R have no matching tuples in S in the left outer join.
  • Left outer Join is denoted by⟕ .

Example

We will use the EMPLOYEE table and FACT_WORKERS table as illustrated above.

Operation:

(EMPLOYEE ⟕FACT_WORKERS)

Emp_Name Street City Branch Salary
Jai Civil line Mumbai B1 20000
Bkadam Park Street Kolkata B2 30000
Asmita Nehru Street Hyderabad B3 60000
Vivek M.G. Street Delhi Null Null

 

Right Outer Join

  • The set of tuples of all combinations in Rand S which are equal on their common attribute names are called the right outer join .
  • Tuples in S have no matching tuples in R in the right outer join.
  • Right outer Join is denoted by ⟖.

 

Example

We will use the EMPLOYEE table and FACT_WORKERS table as illustrated above.

Operation:

EMPLOYEE⟖ FACT_WORKERS

OUTPUT:

Emp_Name Branch Salary Street City
Jai B1 20000 Civil Line Mumbai
Bkadam B2 30000 Park Street Kolkata
Asmita B3 60000 Nehru Street Hyderabad
Dubey B4 40000 Null Null

 

Full Outer Join

  • Full outer join is similar to left or right join except for it has all rows from both tables.
  • Tuples in R that have no matching tuples in S and tuples in S that have no matching tuples in R in their common attribute name are known as Full Outer Join.
  • Full outer Join is denoted by ⟗.

 

Example

We will use the EMPLOYEE table and FACT_WORKERS table as illustrated above.

Operation:

EMPLOYEE⟗ FACT_WORKERS

OUTPUT:

Emp_Name Street City Branch Salary
Jai Civil Line Mumbai B1 20000
Bkadam Park Street Kolkata B2 30000
Asmita Nehru Street Hyderabad B3 60000
Vivek M.G. Street Delhi Null Null
Dubey Null Null B4 30000

 

Equi Join

One of the most common join is the equi join also called equi join. It is based on matched data as per the parity condition. The equi join to make use of the comparison operator(=).

 

Example

CUSTOMER RELATION

Class_ID Name
1 Jai
2 Harsis
3 Mahi

 

PRODUCT

Product_ID City
1 Delhi
2 Mumbai
3 Noida

 

Operation:

CUSTOMER ⋈ PRODUCT

Output

Class_ID Name Product_ID City
1 Jai 1 Delhi
2 Harris 2 Mumbai
3 Harris 3 Noida

 

Please Share