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_CodeEmp _Name
101Jai
102Mahesh
103Vishal

 

SALARY

Emp_CodeEmp _Name
10190000
102130000
103125000
 

Operation: (EMPLOYEE⋈SALARY)

RESULT:

Emp_CodeEmp_NameSalary
101Jai90000
102Mahesh130000
103Vishal125000

 

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_NameSalary
Jai90000
Mahesh130000
Vishal125000

 

 

Outer Join

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

Example

EMPLYEE

Emp_NameStreetCity
JaiCivil LineMumbai
BkadamPark StrrtKolkata
VivekM.G. StreetDelhi
AsmitaNehru NagarHyderabad

 

FACT_WORKERS

Emp_NameBranchSalary
JaiB120000
BkadamB230000
DubeyB440000
AsmitaB360000

 

Operation:

(EMPLOYEE ⋈ FACT_WORKERS)

 

OUTPUT:

Emp_NameStreetCityBranchSalary
JaiCivil LineMumbaiB120000
BkadamPark StreetKolkataB230000
AsmitaNehru NagarHyderabadB360000

 

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_NameStreetCityBranchSalary
JaiCivil lineMumbaiB120000
BkadamPark StreetKolkataB230000
AsmitaNehru StreetHyderabadB360000
VivekM.G. StreetDelhiNullNull

 

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_NameBranchSalaryStreetCity
JaiB120000Civil LineMumbai
BkadamB230000Park StreetKolkata
AsmitaB360000Nehru StreetHyderabad
DubeyB440000NullNull

 

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_NameStreetCityBranchSalary
JaiCivil LineMumbaiB120000
BkadamPark StreetKolkataB230000
AsmitaNehru StreetHyderabadB360000
VivekM.G. StreetDelhiNullNull
DubeyNullNullB430000

 

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_IDName
1Jai
2Harsis
3Mahi

 

PRODUCT

Product_IDCity
1Delhi
2Mumbai
3Noida

 

Operation:

CUSTOMER ⋈ PRODUCT

Output

Class_IDNameProduct_IDCity
1Jai1Delhi
2Harris2Mumbai
3Harris3Noida

 

Please Share