Joins in SQL Server 2008

Joins:- When  we want to get the related data from the table or more than one table then we do joining of the tables.There can be different type of join.

1. Inner Join
2. Outer join
3. Self join
4. Cross join

Example:-

First I have created two table for implementing the all joins operations, which are given below:-

• Studentdetails
• scourse 

For Creating the table and inserting the data please follow the below link.

Create and insert the data in table

•  Studentdetails tables data

• scourse tables data

1. ) Inner Join:- In case of Inner join,In output of query, we get only those records whose values is same in all the join tables.

Ex:-

select sid,sname,cname from  studentdetails  join scourse
on id=sid
                         OR
select sid,sname,cname ,sage from scourse c join studentdetails s   
on c.id=s.sid
             OR
select sid,sname,cname from scourse  join studentdetails  
on sid=id

Output:-

2. ) Outer Join:- If we want to get matched as well as unmatched records from join table,we can use outer join. There are three type of outer join which are given below.

• Left outer Join
• Right outer Join
• Full outer join

2.1 ) Left outer Join:- If we want to show matched and unmatched records from left side table and matched record only right side table,then we can use left outer join.
Ex.
select sid,sname,cname,sage from scourse c Left join studentdetails s   
on c.id=s.sid

Output:-

2.2 ) Right outer Join:-
It is opposite to left outer join,Means if we want to show matched ,unmatched records from right side table and matched records only left side table ,then we can use Right outer join.
Ex.
select sid,sname,cname from scourse c Right join studentdetails s   
on c.id=s.sid

2.3 ) Full outer Join:-
If we want to display all matched and unmatched records from left and right side table then ,we can use full outer join. 
Ex.
select sid,sname,cname from scourse c  full outer join studentdetails s   

on c.id=s.sid
Output:-

3. ) Self Join:-  When the related data exist within the same table then we can implement self join.Which are given below:-
Ex.
select s.sid,s.sname,T.sid ,T.sname from studentdetails s join studentdetails T on s.sid =T.sid Output:-

4. ) Cross Join:- If one table is join with another table without any column basis,so the total number of output will be one table row x second table row
Ex.
select sid,sname,cname from scourse c cross join studentdetails s
Output:-

Note:- You can get 42 records from below query also.But both are different.
select*from studentdetails,scourse
For More:-

1. Microsoft Sql Server
2. Create captcha image
3. Constraints in SQL Server
4. Make xml Document
5. Stored procedure