Open In App

Left join and Right join in MS SQL Server

Improve
Improve
Like Article
Like
Save
Share
Report

Prerequisite – Introduction of MS SQL Server

1. Left Join :
A join combines the set of two tables only. A left join is used when a user wants to extract the left table’s data only. Left join not only combines the left table’s rows but also the rows that match alongside the right table.

Syntax –

select select_list 
from table1 left join table2 on join_predicate
                 (OR)
select * 
from table1 right join table2



2. Right Join :
Right join combines the data of the right table and the rows that match both the tables.

Syntax –

select select_list 
from table1 right join table2 on join_predicate
                 (OR)
select * 
from table1 right join table2



Example –
The first table is the Course table which is considered the left table and the second table is the Student table which is considered the right table.

Table – Course

Name Course Age
Aisha CSE 19
Vani ECE 18
Mina EEE 18


Table – Student

Name Rollno Age
Aisha 111 19
Vani 112 18
Mina 113 18


1. Left Join :
Left join is applied to the tables Course and Student and the table below is the result set.

select name, course 
from c.course left join s.student on c.age = s.age 

Name Course Name Course
Aisha CSE Aisha NULL
Vani ECE Vani NULL
Mina EEE Mina NULL

The left table and its corresponding matching rows on the right table are displayed. If a user wants to display the rows only in the left table, where clause can be used in the query. Left join is usually used for a maximum of two tables but in case of SQL Server, it can be used for multiple tables too.


2. Right Join :
Right join is applied to the tables Course and Student and the table below is the result set.

select name, rollno 
from c.course right join s.student on c.age = s.age 

Name Rollno Name Rollno
Aisha 111 Aisha NULL
Vani 112 Vani NULL
Mina 113 Mina NULL

If the tables do not have common rows, it displays the rows as NULL. The right join can also be used for multiple tables.


Last Updated : 15 Jul, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads