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

Left Outer Join
Right Outer Join
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