Joins in Sql Server:
Joins actualy Performs Two or more Tables combined into a single result set.
Joins can also fetch with different dataBase,different Server also.
There are 5 types of joins available in sql server
1) inner join
2) outer join( left outer join,Right outer join)
3) Full Join
4) Cross Join
5) Self Join
1) inner Join : Two tables having the common column values go with inner join.
Example:
select * from emp inner join empsalary
on
emp.empid=empsalary.empid
2) outer join:
Outer join has two sub types left outer join,Right outer join
Left outer join: Taking Left side values of the table checking into right side
select * from emp left outer join empsalary
on emp.empid=empsalary.empid
Right Outer join:Taking Right side values of the table checking left side
select * from emp right outer join empsalary
on emp.empid=empsalary.empid
3) Full join :
Full join is combined with left outer join ,Right outer join.
select * from emp full join employee
on emp.empid=employee.empid
4) cross Join: cross join is an carteasion product ,its like matrix format here it wont come on condidtion
select * from emp cross join employee
Self join :
Self join can check within the table called self join.
|
| Author: Satyanarayan SushilKumar Bajoria 27 Jun 2008 | Member Level: Gold Points : 2 |
Hi,
Nice attempy to explain Join in SQL Server.
If possible explain outer join in brief,you didn't mention anything on outer join.
And also if possible give eample for self join.
Keep posting.
Thanks for sharing your knowledge.
Thanks and Regards
S.S.Bajoria
|
| Author: Sriram 30 Jun 2008 | Member Level: Gold Points : 2 |
joins in sql server
Inner join
Outer join(left outer join,Right outer join)
Full outer Join
Cross join
create table emps(empid int,empname varchar(34))
insert into emps values(103,'ramesh')
create table employee(empid int,salary money)
insert into employee values(104,6000)
select * from emps
select * from employee
inner join:
select * from emps inner join employee on
emps.empid=employee.empid
empid empname empid salary
----------- ---------------------------------- ----------- ---------------------
101 sriram 101 3000.0000
102 ganesh 102 4000.0000
(2 row(s) affected)
left outer join :
select * from emps left outer join employee on
emps.empid=employee.empid
empid empname empid salary
----------- ---------------------------------- ----------- ---------------------
101 sriram 101 3000.0000
102 ganesh 102 4000.0000
103 ramesh NULL NULL
(3 row(s) affected)
right outer join
select * from emps right outer join employee on
emps.empid=employee.empid
empid empname empid salary
----------- ---------------------------------- ----------- ---------------------
101 sriram 101 3000.0000
102 ganesh 102 4000.0000
NULL NULL 104 6000.0000
full join :
select * from emps full outer join employee on
emps.empid=employee.empid
empid empname empid salary
----------- ---------------------------------- ----------- ---------------------
101 sriram 101 3000.0000
102 ganesh 102 4000.0000
103 ramesh NULL NULL
NULL NULL 104 6000.0000
cross join:
select * from emps cross join employee
empid empname empid salary
----------- ---------------------------------- ----------- ---------------------
101 sriram 101 3000.0000
102 ganesh 101 3000.0000
103 ramesh 101 3000.0000
101 sriram 102 4000.0000
102 ganesh 102 4000.0000
103 ramesh 102 4000.0000
101 sriram 104 6000.0000
102 ganesh 104 6000.0000
103 ramesh 104 6000.0000
To Join different DataBase :
select * from master.dbo.emps inner join msdb.dbo.employee on
master.dbo.emps.empid=msdb.dbo.employee.empid
|
| Author: UltimateRengan 30 Jun 2008 | Member Level: Diamond Points : 1 |
This is very good information,Continue posting such useful articles.
|
| Author: Anne Grace 23 Jul 2008 | Member Level: Silver Points : 0 |
good one.keep going
|
| Author: deepakyadav 23 Jul 2008 | Member Level: Bronze Points : 0 |
thank u this really helps me pal
|
| Author: deepakyadav 23 Jul 2008 | Member Level: Bronze Points : 0 |
if possible can u please give me some more details on stored procedures
|