Join in Sql Server

Sql Join: SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables .Tables in a database are often related to each other with keys. 

Types of Join In Sql:  There are four different type join in Sql Server 

  1.   Inner join

  2. Outer Join

             a.    Left  outer join

             b.    Right  outer join

             c.    Full outer join

   3.Cross Join

   4.Self Join

1.Inner Join: Return rows when there is at least one match in both tables

2.Outer Join: There are three different Outer Join methods

a.    LEFT OUTER JOIN
This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

b.    RIGHT OUTER JOIN
This join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.

c.     FULL OUTER JOIN
This join combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match.

Cross Join Cross join is a Cartesian join means Cartesian product of both the tables. This join does not need any condition to join two tables. This join returns records/rows that are multiplication of record number from both the tables means each row on left table will related to each row of right table. Syntax for right outer Join is as

Join Query in Sql Server with Example.......................

Ceate Database ForJoin …………………….

create database ForJoin

Ceate two Tables StuRecord and StuCollege  in Database ForJoin 

create table StuRecord (id int,Name nvarchar(50),City nvarchar(50))

create table StuCollege(id int,CollegeName nvarchar(50),BranchName nvarchar(50))

Insert record in both  Tables StuRecord and StuCollege  in Database ForJoin

insert into StuRecord values(7,'Kush Tiwari','Ghazipur')

insert into StuCollege values(5,'Cetpa','M.C.A')

Select record in both  Tables StuRecord and StuCollege 

select * from  StuRecord

select * from  StuCollege

Select record in both  Tables StuRecord and StuCollege  with Join/Inner Join 

select StuRecord.*,StuCollege.* from StuRecord  join  StuCollege  on StuRecord.id=StuCollege.id

Select record in both  Tables StuRecord and StuCollege  with Join/Inner Join with specific record

select StuRecord.Name,StuCollege.BranchName from StuRecord  join  StuCollege  on StuRecord.id=StuCollege.id

select StuRecord.*,StuCollege.* from StuRecord  inner join  StuCollege  on StuRecord.id=StuCollege.id

select StuRecord.*,StuCollege.* from StuRecord left outer join  StuCollege  on StuRecord.id=StuCollege.id

select StuRecord.*,StuCollege.* from StuRecord right outer join  StuCollege  on StuRecord.id=StuCollege.id

select StuRecord.*,StuCollege.* from StuRecord full join  StuCollege  on StuRecord.id=StuCollege.id

select StuRecord.*,StuCollege.* from StuRecord ,StuCollege /*old  format*/

Select StuCollege.*,StuRecord.* from StuCollege,StuRecord

Select StuRecord.*,StuCollege.*  from  StuRecord cross join StuCollege  /*New format*/

Select StuCollege.*,StuRecord.* from StuCollege cross join StuRecord

select t1.*,StuCollege.* from StuRecord t1   join  StuCollege  on t1.id=StuCollege.id  /*using alias in join query*/

Self Join with Example

Note: In this concept we are discussing about Self Join for example on given table we   need TeamLeaderName instead of TlId  from given same table

create table EmpDetails(Id int,Name nvarchar(50),TLId int)

insert into EmpDetails values(1,'Kush Tiwari',3)

insert into EmpDetails values(2,'Udayan Maiti',3)

insert into EmpDetails values(3,'Anil Singh',0)

select Id,Name,TLid as TeamLeaderId from EmpDetails

--By Nested Query

select  e.Id,e.name,TeamLeaderName=(Select t2.Name from EmpDetails t2 where e.TLId=t2.Id) from EmpDetails e

--By Self Join

select e.id,e.name as Emp_Name,e1.Name as TeamLeaderName from EmpDetails e left outer join EmpDetails e1 on e.TLId=e1.Id