Also how do `LEFT JOIN`

, `RIGHT JOIN`

and `FULL JOIN`

fit in?

# Sql – the difference between “INNER JOIN” and “OUTER JOIN”

databaseinner-joinjoinouter-joinsql

###### Related Question

- Sql – the difference between Left, Right, Outer and Inner Joins?
- Sql – Difference between JOIN and INNER JOIN
- Mysql – INNER JOIN ON vs WHERE clause
- R – How to join (merge) data frames (inner, outer, left, right)
- C# – LEFT OUTER JOIN in LINQ
- Mysql – How to do a FULL OUTER JOIN in MySQL
- Sql – How to delete using INNER JOIN with SQL Server

## Best Solution

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.

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

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

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

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

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

Full outer joinA 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.