What is the difference between “INNER JOIN” and “OUTER JOIN”?

Assuming you're joining on columns with no duplicates, which is a very 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.

Examples

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

Suppose we have 2 tables A & B with similar and distinct records

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;

Inner join between 2 tables

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(+);

Left outer join between two tables

Right outer join

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

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

select a.*, b.* from a,b where a.a(+) = b.b;

Right outer join between two tables

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;

Full outer join between two tables

Note:

In other words Full Outer Join is union of Left Outer Join and Right Outer Join

Following is the another way to writing the Full Outer Join query

select * from a LEFT OUTER JOIN b on a.a = b.b UNION select * from a RIGHT OUTER JOIN b on a.a = b.b