Javatpoint Logo
Javatpoint Logo

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:


DBMS Join Operation

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:

  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:

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





Please Share

facebook twitter google plus pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA