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

Abhinaya Koshy 1 Reputation point
2022-04-12T08:00:53.053+00:00

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

Azure Database for MySQL
Azure Database for MySQL
An Azure managed MySQL database service for app development and deployment.
747 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,121 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Anurag Sharma 17,581 Reputation points
    2022-04-12T08:57:59.427+00:00

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

    Lets consider 2 tables with below structure:

    192213-image.png

    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:

    192281-image.png

    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  
    

    192301-image.png

    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  
    

    192262-image.png

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

    ----------

    If answer is helpful please click on 192243-image.png 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!

    0 comments No comments