Joins in SQL Server - Part 2

In this blog I am explaining about join and also how to write the query for different types of join.

Before reading this blog, I highly recommend reading the previous part of the series,

• How to work with Join in sql server Part 1

After creating table we will be discuss Join.

Inner Join:

Inner join use to combine the data from one table itself only.

select e1.ename,e1.designation,e2.ename as Manager
from Employee e1,Employee e2
where e1.eid=e2.mgrid

or

select e1.ename,e1.designation,e2.ename as Manager
from Employee e1
Self join Employee e2
On e1.eid=e2.mgrid

Run this self-join output will be as:



Inner-join

Inner join use to display common data from both of tables.

select e.ename,e.designation,b.bid,b.bname
from Employee e
innerjoin branch b
on e.eid=b.eid

Execute this query . output will be like:


                                                            Figure: Inner join

Left outer join:

Left outer join use to display all data from left table and matching data of right table.

select e.ename,e.designation,b.bid,b.bname
from Employee e
leftouterjoin branch b
on e.eid=b.eid

                                                      Figure: Output Left outer join

Right outer join:

Right outer join use to display all data from right side table and matching data of left side table.

select e.ename,e.designation,b.bid,b.bname
from Employee e
Rightouterjoin branch b
on e.eid=b.eid

Execute this query, output will be like below figure:


                                                      Figure: Output right outer join