explain Join in sqlserver

Posted by Sumit.Bundiwal under Sql Server on 7/25/2012 | Points: 10 | Views : 1346 | Status : [Member] | Replies : 10
I have idea of joins But i want to know when to use or why to use each type of join. When i have to use inner join,outter join,self join and cross join with example.











thanks in advaced

sumit bundiwal


Responses

Posted by: Karthikeyanp365 on: 7/25/2012 [Member] Starter | Points: 25

Up
0
Down
Hi check out below link they explained all the joins with simple example
http://www.w3schools.com/sql/sql_join.asp

Sumit.Bundiwal, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ranjeet_8 on: 7/25/2012 [Member] [MVP] Gold | Points: 25

Up
0
Down
One more link.
it will help u to know more about the joins in sql
http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/

Sumit.Bundiwal, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bhupentiwari on: 7/25/2012 [Member] Starter | Points: 25

Up
0
Down
hi
refer this link

http://www.dotnetfunda.com/articles/article1208-different-types-of-join-in-sql-server.aspx

Thanks n Regards
Bhupendra Tiwari

Sumit.Bundiwal, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: GSM_GSV on: 7/25/2012 [Member] Starter | Points: 25

Up
0
Down
If you know about joins, then you should know when to use.

Anyhow, refer the below site to know more about joins and also you can practice online and check results with larger database schema.

http://sqlzoo.net/


---------------------------------------
Live the life you've dreamed

Regards
MADHU

Sumit.Bundiwal, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 7/25/2012 [Member] [MVP] [Administrator] NotApplicable | Points: 25

Up
0
Down
Hi All

Can you please post an Explanation instead of a link ? people should come to our side to get answers not to be refereed to other sides. Please note that the winner of the month will be
selected based on Explanation provided not by the links provided. I don't mean that you must not provide the Links , but spent time writing an answer than providing a link

Thanks

Thank you for posting at Dotnetfunda
[Administrator]

Sumit.Bundiwal, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Patel28rajendra on: 7/26/2012 [Member] Starter | Points: 25

Up
0
Down
Hi

Join : Join is used to get record from two or more tables, depending on the relationship.

There are different types of Joins,But Generally used are
1. Inner Join
2. Outer Join
2.1 Left Outer Join
2.2 Right Outer Join
2.3 Full Outer Join
3. Cross Join
4. Self Join

1. Inner Join
It Returns rows when there is at least one match in both tables

Select * from Table1 a INNER JOIN Table2 b ON a.Col1=b.Col1


2.1 Left Outer Join
Left Outer 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.

Select * from Table1 a LEFT OUTER JOIN Table2 b ON a.Col1=b.Col1


2.2 Right Outer Join
Right Outer 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.

Select * from Table1 a RIGHT OUTER JOIN Table2 b ON a.Col1=b.Col1


2.3 Full Outer Join
Full Outer 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.

Select * from Table1 a FULL OUTER JOIN Table2 b ON a.Col1=b.Col1


3.Cross Join
Cross join is a Cartesian join that does not necessitate any condition to join. The resultset contains records that are multiplication of record number from both the tables.


For more reference see this links

http://www.codeproject.com/Articles/102805/SQL-Joins
http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/

R D Patel

Sumit.Bundiwal, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kirthiga on: 7/26/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,

Joins are used to connect two or more tables. Based on our requirement we can use different types of joins.
Consider two tables #t1 and #t2

create table #t1 (Code varchar(5), Name varchar(10),GameCode varchar(3))

insert into #t1 values ('A0001','Sachin','P01'),('A0002','Anand','P02'),('A0003','Boopathi','P03')
create table #t2 (GameCode varchar(3), Name varchar(10))
insert into #t2 values('P01','Cricket'),('P02','Chess'),('P04','FootBall')


Lets see how to use each join.

Inner Join:

If we want exactly the records that contains in both the table inner join is used.

select * from #t1 a inner join #t2 b on a.GameCode=b.GameCode


Code Name GameCode GameCode Name
A0001 Sachin P01 P01 Cricket
A0002 Anand P02 P02 Chess


Left Outer Join:

If we want all the records in the first table then use Left outer join.

select * from #t1 a left outer join #t2 b on a.GameCode=b.GameCode


Name GameCode GameCode Name
A0001 Sachin P01 P01 Cricket
A0002 Anand P02 P02 Chess
A0003 Boopathi P03 NULL NULL


Right Outer Join:

If we want all the records in the second table then use Right outer join.

select * from #t1 a right outer join #t2 b on a.GameCode=b.GameCode


Code Name GameCode GameCode Name
A0001 Sachin P01 P01 Cricket
A0002 Anand P02 P02 Chess
NULL NULL NULL P04 FootBall


Full Outer Join:

It returns all records in both the table with NULL values for not matching cases.

select * from #t1 a full outer join #t2 b on a.GameCode=b.GameCode


Code Name GameCode GameCode Name
A0001 Sachin P01 P01 Cricket
A0002 Anand P02 P02 Chess
A0003 Boopathi P03 NULL NULL
NULL NULL NULL P04 FootBall


Cross Join:

It gives the multiple of first and second table records

select * from #t1 cross join #t2 


Code Name GameCode GameCode Name
A0001 Sachin P01 P01 Cricket
A0002 Anand P02 P01 Cricket
A0003 Boopathi P03 P01 Cricket
A0001 Sachin P01 P02 Chess
A0002 Anand P02 P02 Chess
A0003 Boopathi P03 P02 Chess
A0001 Sachin P01 P04 FootBall
A0002 Anand P02 P04 FootBall
A0003 Boopathi P03 P04 FootBall


Sumit.Bundiwal, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 9/4/2012 [Member] Bronze | Points: 25

Up
0
Down

create table table11(id integer identity,name varchar(50),address varchar(50))

insert into table11 values('d','coimbatore')
select * from table11

create table table22(id integer identity,name varchar(50),salary integer)
insert into table22 values('q',3000)

select * from table22

outer join three types they are left,right,full outer join

Left Join:
select table11.name,table22.name from table11 left join table22 on table11.name=table22.name

Right join:
select table11.name,table11.address ,table22.name from table11 right join table22 on table11.name=table22.name

Full Outer join:
select table11.name,table11.address,table22.name,table22.salary from table11 full outer join table22 on table11.name=table22.name

// inner join it display only matched items
select table11.name ,table22.name from table11 inner join table22 on table11.name=table22.name

//cross join 0,0 0,1 1,0 1,1

select table11.name ,table22.name ,table11.id,table22.id,table11.address,table22.salary from table11 cross join table22

Sumit.Bundiwal, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Maheshvishnu on: 9/5/2012 [Member] Starter | Points: 25

Up
0
Down
Refer these links

http://www.w3schools.com/sql/sql_join.asp

http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/

mahesh

Sumit.Bundiwal, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response