Difference between Inner Join, Right Outer Join, Left Outer Join, Full Outer Join, Self Join in Sql

One of the most common interview questions that is being asked is explain Inner Join, Right Outer Join, Left Outer Join, Full Outer Join, Self Join in Sql. I will break the question in different meaningful easy to understand concepts.

What is sql join?
Join, as the name suggests, is used to combine data from two tables based on some common features(columns). The resulting data is the temporary table created by comparing two tables.

Consider the below tables
Employee

EmpId	EmpName
1	John
2	David
3	Peter
4	Eric

Area

AreaId	AreaName
3	New York
4	Canada
5	Australia
6	England

Inner join
An inner join between two tables give the intersection of table 1 and table 2. It returns the rows that gets matched based on the join predicate i.e. it will return the row where the join condition gets satisfied.

Example query: Select emp.EmpId , emp.EmpName, area.AreaId, area.AreaName from Employee emp
Inner Join Area area on area.areaId = emp.EmpId

Result:

emp.EmpId	emp.EmpName	area.AreaId	area.AreaName
3	Peter	3	New York
4	Eric	4	Canada

---

---

---

---

Left outer join
Left outer join gives all the rows that matches between the two tables, plus all the rows from left table with corresponding null values from right table. This is inner join + rows from left table with corresponding null values from right table.

Example query: Select emp.EmpId , emp.EmpName, area.AreaId, area.AreaName from Employee emp
LEFT OUTER JOIN Area area on area.areaId = emp.EmpId

Result:

emp.EmpId	emp.EmpName	area.AreaId	area.AreaName
1	John	NULL	NULL
2	David	NULL	NULL
3	Peter	3	New York
4	Eric	4	Canada

---

---

---

---

Right outer join
Right outer join gives all the rows that matches between the two tables, plus all the rows from right table with corresponding null values from left table. This is inner join + rows from right table with corresponding null values from left table.

Example query: Select emp.EmpId , emp.EmpName, area.AreaId, area.AreaName from Employee emp
RIGHT OUTER JOIN Area area on area.areaId = emp.EmpId

Result:

emp.EmpId	emp.EmpName	area.AreaId	area.AreaName
3	Peter	3	New York
4	Eric	4	Canada
NULL	NULL	5	Australia
NULL	NULL	6	England

---

---

---

---

Full outer join
Full outer join gives union of two tables i.e. Left outer join + right outer join, and for unmatched rows, all the rows from right table with corresponding null values from left table, and all the rows from left table with corresponding null values from right table

Example query: Select emp.EmpId , emp.EmpName, area.AreaId, area.AreaName from Employee emp
FULL OUTER JOIN Area area on area.areaId = emp.EmpId

Result:

emp.EmpId	emp.EmpName	area.AreaId	area.AreaName
1	John	NULL	NULL
2	David	NULL	NULL
3	Peter	3	New York
4	Eric	4	Canada
NULL	NULL	5	Australia
NULL	NULL	6	England