Explain the different types of joins

Editorial / Best Answer

krishna420  

  • Member Since Jul-2008 | Jul 23rd, 2008


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.

Showing Answers 1 - 9 of 9 Answers

jagadish babu

  • May 23rd, 2005
 

Basically Joins are used to get result from two or more tables and there are two types of joins  
inner join and outer join  
Inner join : a join of two or more tables which omits the blank rows while checking 
 
Outer join is subcatogorised in to left outer join and right outer join. Which includes blank rows in specifed side if condition satisfies.  
Simple outer join is combination of left and right outerjoins. 
Apart from these there are  
Natural join : cartisian product 
Equi join : which includes = operator in condition 
NonEqui join : All conditional joins which doesn't uses = in there conditions. 

M.Aswin chokkanathan

  • Aug 1st, 2005
 

I want clear explanation about joins with examples.

  Was this answer useful?  Yes

prathima

  • Sep 9th, 2005
 

Diffrent type of joins?
Natural join?
Inner join?
outer join ?
cross join?
equi join?
non equi join?
self join?
to know the details go through the link given below?

  Was this answer useful?  Yes

kishorebabukm

  • Sep 20th, 2005
 

There are different type of ansi joins like,

 1.Natural join,2.Inner join, 3.outer join,4.equi join,5.non equi join,6.simple join,7.cross join,8.full join ( 8i and above)

  Was this answer useful?  Yes

Radhika

  • Dec 20th, 2005
 

Totaly 2 types of joins,

Inner join

Outer join

Self join

  Was this answer useful?  Yes

sankars

  • Jan 26th, 2006
 

prathima very very thanks to u i got more infomation from that site u have given previously

  Was this answer useful?  Yes

Jagan

  • Mar 21st, 2006
 

HI

Excellent Information...

  Was this answer useful?  Yes

Satyajit podder

  • May 24th, 2006
 

Dear Friend,I need to know different type of join .

  Was this answer useful?  Yes

Satyajit podder

  • May 24th, 2006
 

Hello,I need to know the different types of joins in sql server.

  Was this answer useful?  Yes

mktalam

  • Mar 13th, 2008
 

Natural join,2.Inner join, 3.outer join,4.equi join,5.non equi join,6.simple join,7.cross join,8.full join,9.Exception Join


  Was this answer useful?  Yes

krishna420

  • Jul 23rd, 2008
 

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.

  Was this answer useful?  Yes

siva

  • Nov 24th, 2011
 

Mainly we uses some joins in the real time environment..

that is 1) Inner join
2) left outer join
3) right outer join
4) full outer join
i will explain in easy way so that u can understand easily...

1) Inner join: ex .. If we have two tables if we want to join the tables it should be a common column.by using the common column we can join two tables..
if we join two table means ..the records will be executed which are common from both the tables..
Ex : Table A contains 1 2 3 4 5
table B contains 3 4 5 6 7
Then common records 3 4 and 5 will be executed..

2) Left outer join: The records will be executed only which are in left side table , those are not in right hand table..

Ex : Table A contains 1 2 3 4 5
Table B contains 3 4 5 6 7
Then 1 and 2 only executed because 3 4 and 5 are in right table..

3) Right outer join : the records will be executed only which are in right table ,those are not in left hand side ..

Ex Table A contains 1 2 3 4 5
Table B contains 3 4 5 6 7
then 6 and 7 will be executed because 3 4 and 5 are in left hand side table

4) Full outer join: It will execute both matching and non matching records so total records will executed

Ex Table A contains 1 2 3 4 5
Table B contains 3 4 5 6 7
then 1 2 3 4 5 6 and 7 will be executed ...

  Was this answer useful?  Yes

vamsi krishna devineni

  • Nov 27th, 2011
 

Mainly we uses some joins in the real time environment..

that is 1) Inner join
2) left outer join
3) right outer join
4) full outer join
5) cross join
i will explain in easy way so that u can understand easily...

1) Inner join: ex .. If we have two tables if we want to join the tables it should be a common column.by using the common column we can join two tables..
if we join two table means ..the records will be executed which are matched records from both the tables..

Ex : Table A contains 1 2 3 4 5
table B contains 3 4 5 6 7
Then common records 3 4 5 will be executed.. because these are matched records of both tables

ex syntax:- select table1.col1,table1.col2,table2.col1,table2,col2 from table1 inner join table2 on table1.col3=table2.col3;

2) Left outer join: This is extension of inner join. i.e matched records of both tables and unmatched records of left side table..

Ex : Table A contains 1 2 3 4 5
Table B contains 3 4 5 6 7
Then common records 3 4 5 and left table records 1 2 will be executed.. because these are matched records of both tables and
unmatched records of left side table..

ex syntax:- select table1.col1,table1.col2,table2.col1,table2,col2 from table1 left outer join table2 on table1.col3=table2.col3;

3) Right outer join : This is extension of inner join. i.e matched records of both tables and unmatched records of right side table..

Ex Table A contains 1 2 3 4 5
Table B contains 3 4 5 6 7
Then common records 3 4 5 and right table records 6 7 will be executed.. because these are matched records of both tables and
unmatched records of right side table..

ex syntax:- select table1.col1,table1.col2,table2.col1,table2,col2 from table1 right outer join table2 on table1.col3=table2.col3;

4) Full outer join: It will execute both matching and non matching records so total records will executed

Ex Table A contains 1 2 3 4 5
Table B contains 3 4 5 6 7
then 1 2 3 4 5 6 7 will be executed ...

ex syntax:- select table1.col1,table1.col2,table2.col1,table2,col2 from table1 full outer join table2 on table1.col3=table2.col3;

5) Cross join: it is a carteasion product like A X B. Every record in first table join with every record in second table..
If we have two tables if we want to join the tables with out using join condition (or) the two tables does not have
common column but we want to join two tables that time automatically cross join performed between two tables...

Ex : Table A contains 2 records
table B contains 5 records
then 2 X 5 records will executed....

ex syntax:- select table1.col1,table1.col2,table2.col1,table2,col2 from table1 cross join table2

  Was this answer useful?  Yes

zunaid

  • Oct 12th, 2012
 

Different types of joins

Natural join
Inner join
outer join
cross join
equi join
non equi join
self join

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions