Join Operations:

A Join operation combines related tuples from different relations, if and only if a given join condition is satisfied. It is denoted by ⋈.

Example:

EMPLOYEE

SALARY

Operation: (EMPLOYEE ⋈ SALARY) 

Result:

Types of Join operations:

1. Natural Join:

• A natural join is the set of tuples of all combinations in R and S that are equal on their common attribute names.
• It is denoted by ⋈.

Example: Let's use the above EMPLOYEE table and SALARY table:

Input:

∏EMP_NAME, SALARY (EMPLOYEE ⋈ SALARY)

Output:

2. Outer Join:

The outer join operation is an extension of the join operation. It is used to deal with missing information.

Example:

EMPLOYEE

FACT_WORKERS

Input:

(EMPLOYEE ⋈ FACT_WORKERS)

Output:

An outer join is basically of three types:

1. Left outer join
2. Right outer join
3. Full outer join

a. Left outer join:

• Left outer join contains the set of tuples of all combinations in R and S that are equal on their common attribute names.
• In the left outer join, tuples in R have no matching tuples in S.
• It is denoted by ⟕.

Example: Using the above EMPLOYEE table and FACT_WORKERS table

Input:

EMPLOYEE ⟕ FACT_WORKERS 

b. Right outer join:

• Right outer join contains the set of tuples of all combinations in R and S that are equal on their common attribute names.
• In right outer join, tuples in S have no matching tuples in R.
• It is denoted by ⟖.

Example: Using the above EMPLOYEE table and FACT_WORKERS Relation

Input:

EMPLOYEE ⟖ FACT_WORKERS

Output:

c. Full outer join:

• Full outer join is like a left or right join except that it contains all rows from both tables.
• In full outer join, 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.
• It is denoted by ⟗.

Example: Using the above EMPLOYEE table and FACT_WORKERS table

Input:

EMPLOYEE ⟗ FACT_WORKERS

Output:

3. Equi join:

It is also known as an inner join. It is the most common join. It is based on matched data as per the equality condition. The equi join uses the comparison operator(=).

Example:

CUSTOMER RELATION

PRODUCT

Input:

CUSTOMER ⋈ PRODUCT  

Output: