Differences between Inner join and outer join

[Former Member]

Please any one tell what are the differences between Inner join and Outer join plz tell me

points will be rewarded

[Former Member]

Hi

You use INNER JOIN to return all rows from both tables where there is a match. ie. in the resulting table all the rows and colums will have values.

In OUTER JOIN the relulting table may have empty colums. Outer join may be either LEFT or RIGHT

LEFT OUTER JOIN returns all the rows from the first table, even if there are no matches in the second table.

RIGHT OUTER JOIN returns all the rows from the second table, even if there are no matches in the first table.

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

1 xxx

2 yyy

3 zzz

4 www

stud2 : id name

1 aaa

2 bbb

3 ccc

4 ddd When we use Left Outer Join we get the output as:

1 aaa

2 bbb

3 <Null>

4 ccc

When we use Right Outer Join we get the output as:

1 aaa

2 bbb

4 ccc

<Null> ddd

When we use Full Outer Join we get the output as:

1 aaa

2 bbb

3 <Null>

4 ccc

<Null> ddd

Edited by: Jyothsna M on Mar 25, 2008 4:16 AM