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
| 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 |