Join is a query that combines the data from 2 or more tables, sheets, files in a single datasource based on the join condition
Rules to perform Join:
To perform Join we are having two rules
1) Both the tables should have a common column
2) Based on the common data type in the both tablesJoin Types:
Depending on the join condition. Joins are divided into two types
1) Equi Joins
2) Non-Equi Joins
Equi Joins: In the Join condition if you use the equality operator (“=”) then such a kind of Join is called as “ Equi Join”
Non-Equi Joins: In the Join condition apart from the equality if we use any operator like <,>,<>,<=,>= etc.
Suck kind of join is known as “Non-equi Join”
Equi Join Types:
Equi Join is divided into 3 types
1. Inner Join
2. Outer Join
3. Self Join
Again outer join is divided into 3 different types
1. Left Outer
2. Right Outer
3. Full Outer
Inner Join:
Inner Join combines the data from two tables and it display only the matching records (or) common records.
Join Condition: Table1.ID=Table2.ID
Table 1:
Name | ID |
Ramesh | 1 |
Ravi | 2 |
Praveen | 3 |
Kiran | 4 |
Table 2:
ID | Designtion |
1 | Professor |
2 | Professor |
3 | Professor |
4 | Principal |
Output:
Name | Designation |
Ramesh | Professor |
Ravi | Professor |
Praveen | Professor |
Subscribe to our youtube channel to get new updates..!
Outer Join
Left Outer Join:
Is is display the complete data from the left table and matching records from the the Right Table.
Table1.ID=Table2.ID(+)
Following Above Table 1 & Table 2
Output:
Name | Designation |
Ravi | Professor |
Ramesh | Professor |
Praveen | Professor |
Kiran | Null |
Right Outer Join:
It is display complete data from right table and matching records from left table.
Table1.ID(+)=Table2.ID
Following Above Table 1 & Table 2
Output:
Name | Designation |
Ramesh | Professor |
Ravi | Professor |
Praveen | Profess |
Null | Principal |
Full Outer Join:
It displays the complete data from both the tables
Following Above Table 1 & Table 2
Output:
Name | Designation |
Ramesh | Professor |
Ravi | Professor |
Praveen | Professor |
Kiran | Null |
Null | Principla |
Self Join:
Performing a join to the same table itself is known as “Self Join”