Thursday, February 25, 2010

Difference between outer join and inner join

Inner join:


We use this when we compare two colums from two different table .Based on equality or non equality, we retrieve the rows matched.
eg.

Select emp.empid , order.orderid
from emp Innerjoin order
on Emp.empid=order.empid

This example gives all the rows from emp,order tables where the empid's in both the tables are same.





Outer Join:

There are three types of outer joins namely:
Left Outer Join---For retreiving all the columns from the first table irrespective of the column match.
Right Outer Join---For retreiving all the columns from the second table irrespective of the column match
Full Outer Join---For retreiving all the columns from both the tables irrespective of column match.

Eg.

If we have two tables named stud1,stud2 with the following data

Stud1: id Name stud2: id Name
1 xxx 1 aaa
2 yyy 2 bbb
3 zzz 4 ccc
4 www 6 ddd
When we use Left Outer Join we get the output as:
1 aaa
2 bbb
3
4 ccc


When we use Right Outer Join we get the output as:
1 aaa
2 bbb
4 ccc
ddd


When we use Full Outer Join we get the output as:
1 aaa
2 bbb
3
4 ccc
ddd

No comments:

Post a Comment

 
Locations of visitors to this page