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
| EMP_CODE | EMP_NAME |
|---|---|
| 101 | Stephan |
| 102 | Jack |
| 103 | Harry |
SALARY
| EMP_CODE | SALARY |
|---|---|
| 101 | 50000 |
| 102 | 30000 |
| 103 | 25000 |
Result:
| EMP_CODE | EMP_NAME | SALARY |
|---|---|---|
| 101 | Stephan | 50000 |
| 102 | Jack | 30000 |
| 103 | Harry | 25000 |
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:
Output:
| EMP_NAME | SALARY |
|---|---|
| Stephan | 50000 |
| Jack | 30000 |
| Harry | 25000 |
2. Outer Join:
The outer join operation is an extension of the join operation. It is used to deal with missing information.
Example:
EMPLOYEE
| EMP_NAME | STREET | CITY |
|---|---|---|
| Ram | Civil line | Mumbai |
| Shyam | Park street | Kolkata |
| Ravi | M.G. Street | Delhi |
| Hari | Nehru nagar | Hyderabad |
FACT_WORKERS
| EMP_NAME | BRANCH | SALARY |
|---|---|---|
| Ram | Infosys | 10000 |
| Shyam | Wipro | 20000 |
| Kuber | HCL | 30000 |
| Hari | TCS | 50000 |
Input:
Output:
| EMP_NAME | STREET | CITY | BRANCH | SALARY |
|---|---|---|---|---|
| Ram | Civil line | Mumbai | Infosys | 10000 |
| Shyam | Park street | Kolkata | Wipro | 20000 |
| Hari | Nehru nagar | Hyderabad | TCS | 50000 |
An outer join is basically of three types:
- Left outer join
- Right outer join
- 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:
| EMP_NAME | STREET | CITY | BRANCH | SALARY |
|---|---|---|---|---|
| Ram | Civil line | Mumbai | Infosys | 10000 |
| Shyam | Park street | Kolkata | Wipro | 20000 |
| Hari | Nehru street | Hyderabad | TCS | 50000 |
| Ravi | M.G. Street | Delhi | NULL | NULL |
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:
Output:
| EMP_NAME | BRANCH | SALARY | STREET | CITY |
|---|---|---|---|---|
| Ram | Infosys | 10000 | Civil line | Mumbai |
| Shyam | Wipro | 20000 | Park street | Kolkata |
| Hari | TCS | 50000 | Nehru street | Hyderabad |
| Kuber | HCL | 30000 | NULL | NULL |
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:
Output:
| EMP_NAME | STREET | CITY | BRANCH | SALARY |
|---|---|---|---|---|
| Ram | Civil line | Mumbai | Infosys | 10000 |
| Shyam | Park street | Kolkata | Wipro | 20000 |
| Hari | Nehru street | Hyderabad | TCS | 50000 |
| Ravi | M.G. Street | Delhi | NULL | NULL |
| Kuber | NULL | NULL | HCL | 30000 |
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
| CLASS_ID | NAME |
|---|---|
| 1 | John |
| 2 | Harry |
| 3 | Jackson |
PRODUCT
| PRODUCT_ID | CITY |
|---|---|
| 1 | Delhi |
| 2 | Mumbai |
| 3 | Noida |
Input:
Output:
| CLASS_ID | NAME | PRODUCT_ID | CITY |
|---|---|---|---|
| 1 | John | 1 | Delhi |
| 2 | Harry | 2 | Mumbai |
| 3 | Harry | 3 | Noida |