## TUTORIAL SQL -What is the difference between “INNER JOIN” and “OUTER JOIN”?

on Wed Apr 11, 2018 5:33 pm

Also how do LEFT JOIN, RIGHT JOIN and FULL JOIN fit in?

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:

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.

Left outer join

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

Right outer join

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

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.

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:

- Code:
`A B`

- -

1 3

2 4

3 5

4 6

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.

- Code:
`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

Left outer join

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

- Code:
`select * from a LEFT OUTER JOIN b on a.a = b.b;`

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

a | b

--+-----

1 | null

2 | null

3 | 3

4 | 4

Right outer join

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

- Code:
`select * from a RIGHT OUTER JOIN b on a.a = b.b;`

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

a | b

-----+----

3 | 3

4 | 4

null | 5

null | 6

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.

- Code:
`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

**Permissions in this forum:**

**cannot**reply to topics in this forum