# What is the difference between INNER JOIN and OUTER JOIN?

What is the difference between INNER JOIN and OUTER JOIN in SQL?

Assuming youâ€™re joining on columns with no duplicates, which is by far the most common case:

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.

[b]Examples[/b]

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.

[b]inner join[/b]

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

[b]full outer join[/b]

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