How LEFT JOIN and RIGHT JOIN works

As usual, we are keeping the same two table Employee and location to explain left and right joins.

Employee Table

empId	EmpName
101	Rajesh
102	Vinodh
103	Kumar
104	Mukesh

Location Table

empId	Location
101	Chennai
102	Mumbai
103	Kolkatta
105	Hyderabad

What is LEFT Join ?

The LEFT JOIN keyword returns all rows from the left table (What is left table ? ), with whatever matching rows in the right table (What is right table ?).

Suppose if there is no match in the right side, result is NULL.

We can write query first for this left join, after that I will explain. Left join query will be,

select * from employee left join location 
on employee.empID = location.empID;

Output : 

Employee.empID	Employee.empName	Location.empID	Location.empLocation
101	Rajesh	101	Chennai
102	Vinodh	102	Mumbai
103	Kumar	103	Kolkatta
104	Mukesh	Null	Null

From the result, we able to understand , left join query taking the rows from left table completely. If there is no match in the right tale it will return null like above.

Ven Diagram 

Left Join Ven diagram

What is Right Join ?

The RIGHT JOIN returns all rows from the right table with the matching rows in the left table. If there is no match in the left table it will return NULL. 

Ven diagram of Right Join

Right Join Ven diagram

 

Right Join Query

select * from employee right join location
on employee.empID = location.empID; 

Below figure represents some clear idea about left and right join.  

Table pointing identification of right and left join