Difference Between Left And Right Outer Join in SQL Server

At the early stages, it’s confusing what left and right join is actually all about, and which to apply where and what will be the outcome for the same.

Today I will try to show with an example what the difference between left and right join actually is with practical examples.

Create two tables and we call it A and B. Both will have two fields, i.e., id and name.

create table A(IDint, Name varchar (10))
GO

create table B(IDint, Name varchar (10))

Let us insert a few values in that. Right click on that table name and select option “Edit top 200 rows”. Now manually insert values in that as shown below:



We have two records in A and three records in B.

Left Join

Fetches data from the left table no matter that data is there in the right table. If data is there in the right table it will show the same, else it will show the null values.

Right Join

Fetches the data from the right table no matterthat data is there in the left table. If data is there in the left table, it will show the same, else it will show the null values.



Let us see the result of the queries:

1. Query:
   

   Select * FROM A leftJOIN B ON A.id = B.id

   Output:
   
   
   
   Description: We have two records in table 1, so left join will match the same in the table 2, and hence it found the matching records in table two.
   
   
2. Query:
   

   SELECT * FROM B RightJOIN A on B.id = A.id

   Output:
   
   
   
   Description: Here the output for both the queries is the same. In the second query we are doing right join with table A and table A is having 2 records.
   
   
3. Query:
   

   Select*from A rightjoin B on A.id = B.id

   Output:
   
   
   
   Description: In this query we are doing right join with table B which is having 3 records. So in the output it showed the record 3rd from table B. But in table A no matching records were found, so it showed null values for the same.
   
   
4. Query:
   

   select * from B LEFTjoin A on A.id = B.id

   Output:
   
   
   
   Description: In this query we are doing left join with table B which is having 3 records. So in the output it showed the record 3rd from table B. But in table A no matching records were found, so it showed null values for the same.