Understanding Joins

Understanding Inner , Outer , left Outer , Right Outer Joins and Cartesian Product

Left Table

EmployeeId=>EmployeeName  idToName Table     ( rows with Employee Id 3 is repeated )

     Employee ID    Employee Name
          1         Maverick
          2         Aurthur
          3         James
          4         Russel
          3         James

 

Right Table

EmployeeId=>DepartmentName     IdToDepartment Table      ( rows with Employee Id 5 is repeated )

     Employee ID  Department Name
          1        Tech
          4        Business
          5        Sales
          5       Sales

 

[addToAppearHere]

Join (Inner Join ) :  Join  idToName , IdToDepartment  where   idToName. Employee ID     = IdToDepartment.Employee ID

  1.  The rows which are common in both the tables are only considered.
  2. Any repeated rows from either table will get repeated in output if it matches the joining criteria
    Employee ID    Employee Name      Employee ID  Department Name
1 Maverick 1 Tech
 4 Russel 4 Business

 

Outer Join (Full outer) :   Full outer Join  idToName , IdToDepartment  where    idToName. Employee ID     = IdToDepartment.Employee ID

  1. Rows which are not common in both tables do appear in the output .
  2. Any repeated rows from either table will get repeated in output if it matches the joining criteria
    Employee ID    Employee Name      Employee ID  Department Name
1 Maverick 1 Tech
 2 Aurthur null null
3 James null null
4 Russel 4 Business
3 James null null
null null 5 Sales
null null 5 Sales

 

 

Left Outer Join :    Left outer Join  idToName , IdToDepartment  where    idToName. Employee ID     = IdToDepartment.Employee ID

  1.  All elements of the left table will be there . Right table elements which  have no common id will be removed .
  2.  Any repeated rows from either table will get repeated in output if it matches the joining criteria
    Employee ID    Employee Name      Employee ID  Department Name
1 Maverick 1 Tech
 2 Aurthur null null
3 James null null
4 Russel 4 Business
3 James null null

Right Outer Join :  Right outer Join  idToName , IdToDepartment  where    idToName. Employee ID     = IdToDepartment.Employee ID

1. All elements of the Right table will be there . Left table elements which  have no common id will be removed .

2. Any repeated rows from either table will get repeated in output if it matches the joining criteria

[addToAppearHere]

    Employee ID    Employee Name      Employee ID  Department Name
1 Maverick 1 Tech
4 Russel 4 Business
null null 5 Sales
null null 5 Sales

 

Cartesian Product idToName , IdToDepartment  .

It generates  a total of  M * N   rows in output . Where M and N are total rows in  Left Table and Right Table .  There is no condition in Cartesian product

In our case it will 5 * 4 = 20 rows in output .

 

 

Tags: , , , , , ,