What is Join
Join is the operation in SQL/MySQL which is used to combined two tables/database based on certain condition. For example suppose we have two table A and B where A has Employee_ID and Employer_Name where as B has Employee_ID and Employee Name.
Table A
Employee_id Employer_name
7 Oracle
1 HP
2 HP
4 IBM
6 Infosys
Table B
Employee_id Employee_name
1 Salil
2 Avantika
3 Gowri
4 Anil
5 Suraj
Now say we want name of all employees who works in the HP then we can have following join operation
Select B.Employee_name from A join B on A.Employee_id = B.Employee_id
Types of Join
- Inner Join
- Outer Join
a. Left Outer Join
b. Right Outer Join
1. Inner Join
Inner Join is the natural join operation between two or more tables/db where it return only the rows that actually match based on the join predicate. By default if inner word is missing then join is treated as inner join operation.
Syntax
Select A.Emplyee_id, A.Employer_name, B.Employee_name from A join B on A.Employee_id = B.Employee_id
The above can be written as
Select A.Emplyee_id, A.Employer_name, B.Employee_name from A, B where A.Employee_id = B.Employee_id
Output
Employee_id Employer_name Employee_name
1 HP Salil
2 HP Avantika
4 IBM Anil
2. Outer Join
An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Or we can simply say the union of two tables.
Syntax
select A.Emplyee_id, A.Employer_name, B.Employee_name from A OUTER JOIN B on A.Employee_id = B.Employee_id;
Output
Employee_id Employer_name Employee_name
1 HP Salil
2 HP Avantika
4 IBM Anil
7 Oracle NULL
6 Infosys NULL
3 NULL Gowri
5 NULL Suraj
Outer joins subdivide further into left outer joins and right outer joins depending on which table's rows are retained
2.a Left Outer Jojn
A left outer join retains all of the rows of the left table, regardless of whether there is a row that matches on the right table. Check the following example -
Syntax
select A.Emplyee_id, A.Employer_name, B.Employee_name from A left join B on A.Employee_id = B.Emploee_id
Output
Employee_id Employer_name Employee_name
1 HP Salil
2 HP Avantika
4 IBM Anil
7 Oracle NULL
6 Infosys NULL
2.b Right Outer Jojn
A Right outer join retains all of the rows of the right table, regardless of whether there is a row that matches on the left table. Check the following example -
Syntax
select A.Emplyee_id, A.Employer_name, B.Employee_name * from A right join B on A.Employee_id = B.Emploee_id
Output
Employee_id Employer_name Employee_name
1 HP Salil
2 HP Avantika
4 IBM Anil
3 NULL Gowri
5 NULL Suraj
