Tuesday, 21 May 2013

Joins in Sql server

::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

No comments:

Post a Comment

8451831077