Inner join Vs Outer join

Inner join Vs Outer join

This is most common interview question for many developers ... I would like to give a easy understanding of this to remember for life ...

  •     An inner join of A and B gives the result of A intersect B, i.e. the inner part of a venn diagram intersection.
  •     An outer join of A and B gives the results of A union B, i.e. the outer parts of a venn diagram union.

Suppose you have two Tables, with a single column each, and data as follows:

A    B
-    -
1    3
2    4
3    5
4    6

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

Inner join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;
select a.*,b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Left outer join

A left outer join will give all rows in A, plus any common rows in B.

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*,b.*  from a,b where a.a = b.b(+);

a |  b 
--+-----
1 | null
2 | null
3 |    3
4 |    4

Full outer join

A full outer join will give you the union of A and B, i.e. All the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b 
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

 

Inner join

 

Full Outer join

 

Reference : [1]


0 comments to "Inner join Vs Outer join"

Post a Comment

Who ever writes Inappropriate/Vulgar comments to context, generally want to be anonymous …So I hope U r not the one like that?
For lazy logs u can at least use Name/URL option which don’t even require any sign-in, good thing is that it can accept your lovely nick name also and URL is not mandatory too.
Thanks for your patience
~Krishna(I love "Transparency")
వీలయితే నాల్గు పోస్టులు ...కుదురితే ఒక కామెంటూ ...

Translate

Enter your email address:

Buffs ...

Visitors


hits counter
View My StatsCheck Google Page Rank

Add to Google Reader or Homepage

Bookmark and Share

Tags


Powered by WidgetsForFree