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.

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?

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)

Radhika

• Dec 20th, 2005

 

Totaly 2 types of joins,

Inner join

Outer join

Self join

sankars

• Jan 26th, 2006

 

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

Jagan

• Mar 21st, 2006

 

HI

Excellent Information...

Satyajit podder

• May 24th, 2006

 

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

Satyajit podder

• May 24th, 2006

 

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

mktalam

 Profile Answers by 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

krishna420

 Profile Answers by 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.

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 ...

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

zunaid

• Oct 12th, 2012

 

Different types of joins

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