Difference between various types of join in SQL

Assuming you're joining on columns with no duplicates, this the most common case:

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

Examples

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

One     Two
-     -
1     3
2     4
3     5
4     6

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

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 One INNER JOIN Two on One.One = Two.Two;


One  | Two

3  | 3
4  | 4

Left outer join

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

select * from One LEFT OUTER JOIN Two on One.One = Two.Two;


One  |  Two  
--+-----
1  | null
2  | null
3  |    3
4  |    4

Right outer join

A right outer join will give all rows in Two, plus any common rows in One.

select * from One LEFT OUTER JOIN Two on One.One = Two.Two;


One   |  Two  
--+-----

3     |    3
4     |    4
null  |    5 

null  |    6

Full outer join

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

select * from One FULL OUTER JOIN Two on One.One = Two.Two;

 One   |  Two  
-----+-----
   1   | null
   2   | null
   3   |    3
   4   |    4
 null  |    5 

 null  |    6