Joins in SQL Server - Part 2

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

After creating table we will be discuss Join.

Inner Join:

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

  1. select e1.ename,e1.designation,e2.ename as Manager   
  2. from Employee e1,Employee e2  
  3. where e1.eid=e2.mgrid  
or
  1. select e1.ename,e1.designation,e2.ename as Manager  
  2. from Employee e1  
  3. Self join Employee e2  
  4. On e1.eid=e2.mgrid  
Run this self-join output will be as:

Run this self-join

Inner-join

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

Inner join use to display
  1. select e.ename,e.designation,b.bid,b.bname  
  2. from Employee e  
  3. innerjoin branch b  
  4. on e.eid=b.eid  
Execute this query . output will be like:

Inner join
                                                            Figure: Inner join

Left outer join:

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

Left outer join
  1. select e.ename,e.designation,b.bid,b.bname  
  2. from Employee e  
  3. leftouterjoin branch b  
  4. on e.eid=b.eid  
Output Left outer join
                                                      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.
  1. select e.ename,e.designation,b.bid,b.bname  
  2. from Employee e  
  3. Rightouterjoin branch b  
  4. on e.eid=b.eid  
Right outer join

Execute this query, output will be like below figure:

Output right outer join
                                                      Figure: Output right outer join