Left join and Right join in MS SQL Server
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
Share your thoughts in the comments
Please Login to comment...