In SQL, what are the differences between Inner Join, Left Outer Join and Right Outer Join?

Question

Also, what is the correct format for performing a Join?

Answer 1

Hi @Abhinaya Koshy , welcome to Microsoft Q&A forum.

Lets consider 2 tables with below structure:

Inner join: This join will return the data that has one-to-one mapping or matching records present in both the tables. As an example, an inner join on id column of above tables will return all the employee records except Employee 4 as it does not have any record present in EmployeeDetails tables:

select EE.*,EC.phoneNumber from EmployeeEntity EE inner join EmployeeContact EC on EE.id = EC.id  

Result:

Left Outer Join: This will return all the records from the left table (EmployeeEntity) and matching records from the right table (EmployeeContact). Like below query will return all the employee records from EmployeeEntity table but phone number of employee 4 will be null as it is not present in EmployeeContact.

select EE.*,EC.phoneNumber from EmployeeEntity EE left outer join EmployeeContact EC on EE.id = EC.id  

Right Outer Join: This will return all the records from the right table (EmployeeContact) and matching records from the right table (EmployeeEntity). Like below query will return all the employee records from Employeecontact table but employee 4 will not be returned as it is not present in EmployeeEntity table. If there are more records like Employee 5 in Contact table but not in Entity table then this query will return that record as well.

select EE.*,EC.phoneNumber from EmployeeEntity EE right outer join EmployeeContact EC on EE.id = EC.id  

Please let us know if this helps or else we can discuss further on the same.

----------

If answer is helpful please click on as it could help other members of the Microsoft Q&A community who have similar questions and are looking for solutions. Thank you for helping to improve Microsoft Q&A!