_{Translation of the question “Difference between INNER and OUTER joins ” @ cdv .}

Answer 1, authority 100%

You can also use this diagram to understand table joins:

_{Translation of the answer “Difference between INNER and OUTER joins” @ Teoman Shipahi}

Answer 2, authority 98%

Suppose you want to do a column join without duplicates, which is quite common:

• Inner join of A and B: A intersects B, i.e. the interior of the intersection Venn diagrams .

• Outer join A and B: A connects to B, i.e. the outer part of a connection in a Venn diagram.

Examples

Suppose you have two tables. Each consists of one column, with the following values:

A B
- -
13
2 4
3 5
4 6

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

Internal join

An inner join using one of the equivalent queries gives the intersection of two tables, that is, two rows common to each of them.

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

The result of the left outer join is all rows of table A plus all rows of table B that match the rows of table A.

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

The result of the right outer join is all rows of table B plus all rows of table A that match the rows of table B.

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

The result of a full outer join is a join between tables A and B, i.e. all rows of A and all rows of B. If any element of table A does not have a match in table B, that part of B is empty, 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

_{Translation of the answer “Difference between INNER and OUTER joins ” @ Mark Harrison .}

Answer 3, authority 5%

INNER-inner join (intersection of sets) -selects common from sets

LEFT JOIN-left outer join-takes everything on the left and everything on the right, for which there is a match in the left set

Answer 4, authority 2%

What do the differences mean, and also why such names, for all types of table joins, can be clearly seen in the following graph:

• [INNER] JOIN – internal
• (LEFT | RIGHT) [OUTER] JOIN – outer, or right, or left
• FULL [OUTER] JOIN – external, right and left, ie full

Answer 5