SQL Join Command - Inner, Outer and Left Join

In order to avoid data duplication, data is stored in related tables. Join commands like inner join, outer join and left join is used to fetch data from related tables.

Different type of joins are

• Join or inner join
• Outer Join
• Left Join
• Full Join

SQL Join query with examples on Employee and Salary table given below are

Table Name : Employee

Employee_ID	Employee_Name	Age
1	John	28
2	Alex	24
3	James	35
4	Roy	22
5	Kay	44

Table Name : Salary

Employee_ID_Ref	Year	Month	Salary
1	2015	April	30000
1	2015	May	31000
1	2015	June	32000
2	2015	April	40000
2	2015	May	41000
2	2015	June	42000

Inner JOIN

Inner join returns rows when there is at least one match in both tables. SQL query to join above tables is as given below.

select * from employee a join salary b on a.employee_id = b.employee_id_ref

The result of the above given query is as shown below.

Employee_ID	Employee_Name	Age	Employee_ID_Ref	Year	Month	Salary
1	John	28	1	2015	April	30000
1	John	28	1	2015	May	31000
1	John	28	1	2015	June	32000
2	Alex	24	2	2015	April	40000
2	Alex	24	2	2015	May	41000
2	Alex	24	2	2015	June	42000

Left JOIN

Return all rows from the left table, even if there are no matches in the right table. SQL query to left join above tables is as given below.

select * from employee a left join salary b on a.employee_id = b.employee_id_ref

Employee_ID	Employee_Name	Age	Employee_ID_Ref	Year	Month	Salary
1	John	28	1	2015	April	30000
1	John	28	1	2015	May	31000
1	John	28	1	2015	June	32000
2	Alex	24	2	2015	April	40000
2	Alex	24	2	2015	May	41000
2	Alex	24	2	2015	June	42000
3	James	35
4	Roy	22
5	Kay	44

Here, even though there is no matching records for employees "James,Roy and Kay", there names are shown in the results.

Right JOIN

Return all rows from the right table, even if there are no matches in the left table. SQL query to right join above tables are as given below

select * from employee a right join salary b on a.employee_id = b.employee_id_ref

Employee_ID	Employee_Name	Age	Employee_ID_Ref	Year	Month	Salary
1	John	28	1	2015	April	30000
1	John	28	1	2015	May	31000
1	John	28	1	2015	June	32000
2	Alex	24	2	2015	April	40000
2	Alex	24	2	2015	May	41000
2	Alex	24	2	2015	June	42000

Full JOIN

Return rows when there is a match in one of the tables. SQL query to full join above tables are as given below

select * from employee a full join salary b on a.employee_id = b.employee_id_ref

The result of the full join is as shown below.

Employee_ID	Employee_Name	Age	Employee_ID_Ref	Year	Month	Salary
1	John	28	1	2015	April	30000
1	John	28	1	2015	May	31000
1	John	28	1	2015	June	32000
2	Alex	24	2	2015	April	40000
2	Alex	24	2	2015	May	41000
2	Alex	24	2	2015	June	42000
3	James	35
4	Roy	22
5	Kay	44