Joins in Sql server

Joins in Sql server

Description ::

            In this article I can explain all type of join in sql server,with example . for your better understanding
I have created a following tables on which we will perform a join queres.

step 1: Create table

     create table #Name (id int , names varchar(50) )
     create table #NickName (id int , NickName varchar(50))
      create table #selfMaster(id int ,emp_name varchar(50),ManagerId int)

step 2: insert data

         insert into #Name (id,names)values (1,'sandip' ) 
         insert into #Name (id,names)values (2,'aditya' )
         insert into #Name (id,names)values (3,'pranav' )
         insert into #Name (id,names)values (4,'savita' )

         insert into #NickName (id,NickName)values (2,'babu' )
         insert into #NickName (id,NickName)values (3,'chandu' )
         insert into #NickName (id,NickName)values (4,'dhara' )
         insert into #NickName (id,NickName)values (5,'dhara' )

         insert into #selfMaster (id,emp_name,ManagerId) values (1,'sandip' ,2)
         insert into #selfMaster (id,emp_name,ManagerId) values (2,'aditya' ,3)
         insert into #selfMaster (id,emp_name,ManagerId) values (3,'pranav' ,4)
         insert into #selfMaster (id,emp_name,ManagerId) values (4,'savita' ,1)

s

step 3: inner join

An inner join essentially combines the records from two tables (A and B) based on a given join-predicate,
in simple words by using this join we can select a record from both table where a condition is matched .

Example::Inner Join Query

               
              select a.id,names,nickname from #name a
                 inner join #nickname b on a.id=b.id

step 4: Outer Join

In sql server there are three types of outer join used.

Types of Outer joins

Join 1: Left Outer join

     An left outer join is gives you all data ( Records) from left side table and matched data ( Records) from
right side table.

Example::Left Outer join Query

                   select a.id,names,nickname from #name a
              left outer join #nickname b on a.id=b.id

Join 2: Right Outer join

     An Right Outer join is gives you all data ( Records) from Right side table and matched data ( Records)
from  Left side table.

Example::Right Outer join Query

             select a.id,names,nickname from #name a
           right outer join #nickname b on a.id=b.id

Join 3: Full Outer join

          An Full outerjoin is gives you all rows from both tables, regardless of whether or not the other table
has a matching value.

Example::Full Outer join Query

                      select a.id,names,nickname from #name a
           full outer join #nickname b on a.id=b.id

step 5: Self join

               Some time we need to write a join on single table, in short a self join is applicable on two instance
of single table you can check following query for better understanding.

Example:: Self join Query

         select a.id,a.emp_name,b.emp_name Manager_Name from #selfMaster a join   #selfMaster b on b.id=a.ManagerId