Join Operations:
A Join operation combines related tuples from diferent relations, if and only
if a given join condition is satisfedd It is denoted by ⋈d
Example:
EMPLOYEE
EMP_CODE EMP_NAME
101 Stephan
102 Jack
103 Harry
SALARY
EMP_CODE SALARY
101 50000
102 30000
103 25000
Operation: (EMPLOYEE ⋈ SALARY)
Result:
EMP_CODE EMP_NAME SALARY
101 Stephan 50000
102 Jack 30000
103 Harry 25000
Types of Join operations:
1. Natural Join:
o A natural join is the set of tuples of all combinations in R and S that are equal
on their common attribute namesd
o It is denoted by ⋈d
Example: Let's use the above EMPLOYEE table and SALARY table:
Input:
∏EMP_NAME, SALARY (EMPLOYEE ⋈ SALARY)
Output:
EMP_NAME SALARY
Stephan 50000
Jack 30000
Harry 25000
2. Outer Join:
The outer join operation is an extension of the join operationd It is used to
deal with missing informationd
Example:
EMPLOYEE
EMP_NAME STREET CITY
Ram Civil line Mumbai
Shyam Park street Kolkata
Ravi MdGd 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:
(EMPLOYEE ⋈ FACT_WORKERS)
Output:
EMP_NA STREE CITY BRANC SALAR
ME T H Y
Ram Civil Mumbai Infos 100
line ys 00
Shyam Park Kolkata Wipr 200
stre o 00
et
Hari Neh Hyderab TCS 500
ru ad 00
nag
ar
An outer join is basically of three types:
ad Left outer join
bd Right outer join
cd Full outer join
a. Left outer join:
o Left outer join contains the set of tuples of all combinations in R and S that
are equal on their common attribute namesd
o In the left outer join, tuples in R have no matching tuples in Sd
o It is denoted by ⟕d
Example: Using the above EMPLOYEE table and FACT_WORKERS table
Input:
1d EMPLOYEE ⟕ FACT_WORKERS
EMP_NA STREE CITY BRANC SALAR
ME T H Y
Ram Civil Mumbai Infos 100
line ys 00
Shyam Park Kolkata Wipr 200
stre o 00
et
Hari Neh Hyderab TCS 500
ru
stre ad 00
et
Ravi MdGd Delhi NULL NUL
Stre L
et
b. Right outer join:
o Right outer join contains the set of tuples of all combinations in R and S that
are equal on their common attribute namesd
o In right outer join, tuples in S have no matching tuples in Rd
o It is denoted by ⟖d
Example: Using the above EMPLOYEE table and FACT_WORKERS Relation
Input:
EMPLOYEE ⟖ FACT_WORKERS
Output:
EMP_NA BRANC SALAR STREE CITY
ME H Y T
Ram Infos 100 Civil Mumbai
ys 00 line
Shyam Wipr 200 Park Kolkata
o 00 stre
et
Hari TCS 500 Neh Hyderab
00 ru ad
stre
et
Kuber HCL 300 NUL NULL
00 L
c. Full outer join:
o Full outer join is like a left or right join except that it contains all rows from
both tablesd
o 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 named
o It is denoted by ⟗d
Example: Using the above EMPLOYEE table and FACT_WORKERS table
Input:
EMPLOYEE ⟗ FACT_WORKERS
Output:
EMP_NA STREE CITY BRANC SALAR
ME T H Y
Ram Civil Mumbai Infos 100
line ys 00
Shyam Park Kolkata Wipr 200
stre o 00
et
Hari Neh Hyderab TCS 500
ru ad 00
stre
et
Ravi MdGd Delhi NULL NUL
Stre L
et
Kuber NUL NULL HCL 300
L 00
3. Equi join:
It is also known as an inner joind It is the most common joind It is based on
matched data as per the equality conditiond The equi join uses the
comparison operator(=)d
Example:
CUSTOMER RELATION
CLASS_ID NAME
1 John
2 Harry
3 Jackson
PRODUCT
PRODUCT_ID CITY
1 Delhi
2 Mumbai
3 Noida
Input:
CUSTOMER ⋈ PRODUCT
Output:
CLASS_ID NAME PRODUCT_ID CITY
1 John 1 Delhi
2 Harry 2 Mumbai
3 Harry 3 Noida
