Joins in SQL Server

In this article we learn about joins in SQL Server.

A join is used to combined data from two or more tables in SQL.

To provide a sample, first we create two tables called Table1 and Table2.

Create table table1
(
   id int,
   Name varchar(30)
)

Create table table2
(
   id int,
   Name varchar(30),
   data varchar(100)
)

Insert some data into the tables as in the following:

insert into table1 values(1,’test1′)
insert into table1 values(2,’test2′)
insert into table1 values(3,’test3′)

insert into table2 values(1,’test1′,null)
insert into table2 values(3,’test3′,”)
insert into table2 values(4,’test4′,’some text’)
insert into table2 values(5,’test5′,’other text’)

Joins

The following are the various types of joins in SQL Server.

• Cross Join
  
  A cross join that does not have a where clause. It produces the cartesian product of the tables involved in the join.
  
  The output rows of a cross join = number of rows in first_table * number of rows in second_table
  
  Syntax: select * from table1,table2
  
  
• Inner Join
  
  An inner join selects all rows from both tables that have a match in both joined tables.
  
  Syntax: select table1.id,table2.name,table2.data from table1 inner join table2 on table1.id=table2.id
  
  Output
  
                                
  
  
• Left Outer Join
  
  In this join are all the rows in the first table and the matching rows of the second table. An unmatched row in the second table appears as null in the result.
  
  Syntax: select table1.id,table2.name,table2.data from table1 left outer join table2 on table1.id=table2.id
  
  Output
  
                                
  
  
• Right Outer Join
  
  In this join all the rows in the second table and the matching rows of the first table. An unmatched row in the first table appears as null in the result.
  
  Syntax: select table1.id,table2.name,table2.data from table1 right outer join table2 on table1.id=table2.id
  
  Output
  
                                
  
  
• Full Outer Join
  
  In this join all the rows are the result of whether or not they are matched.
  
  Syntax: select table1.id,table2.name,table2.data from table1 full outer join table2 on table1.id=table2.id
  
  Output
  
                                
  
  
• Self Join
  
  In this join one table is joined to itself. We need to create another table for explaining the self join.
  

  create table emp
  (
  id int identity(1,1),
  name varchar(30),
  managerid int
  )
  
  insert into emp values(‘pramod’,0)
  insert into emp values(‘prem’,1)
  insert into emp values(‘sameer’,1)
  insert into emp values(‘ravi’,2)
  insert into emp values(‘rahul’,3)

  Syntax: select distinct e1.id, e1.name from emp e1, emp e2 where e1.id=e2.managerid
  
  Output
  
                                

I hope this article is helpful for you..

Thanks :)