Friday, August 17, 2012

Inner Join and Outer Join


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.


Examples
Suppose you have two Tables, with a single column each, and data as follows:
A    B
-    -
1    3
2    4
3    5
4    6
select * from a INNER JOIN b on 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;
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

No comments:

Post a Comment