Assuming you're doing a joincolumn check with no duplicates, which is a common case:

• An inner join of A and B will return the result of the intersection of sets A and B. In other words, the inner part –intersection– in a Venn diagram.

• A full outer join between A and B will return the result of the union of A and B. In other words, the outer part – join – in a Venn diagram.

Examples:

Suppose we have two tables, with a single column each and the following data:

A    B
-    -
1    3
2    4
3    5
4    6

Note that (1,2) are only found in A, (3,4) are common, and (5,6) are only found in B.

Inner Join

An inner join –using any of the equivalent query syntaxes– gives you the intersection of both tables, that is, the rows that both tables 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

Left outer join

A outer joinfrom the left will give you all the rows of A, including the common rows between A and B.

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 outer joinfrom the right will give you all the rows of B, including the common rows with A.

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 outer joincomplete (full) will give you the union of A and B; that is, all the rows in A and all the rows in B. If a row in A does not have a corresponding row in B, the portion of B 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 |    5
null |    6

Venn diagrams

We can see the same thing with Venn diagrams:

SQL Joins.svg image by Arbeck , shared under CC BY 3.0 license .

I am going to use the same example of jachguate which is very clear adding a few small details.

Glossary

• inner join (free translation: inner join)
• outer join (free translation: outer join)

-- Crear tabla A (tabla Izquierda)
CREATE TABLE A
(
a INT
);

-- Crear tabla B (tabla derecha)
CREATE TABLE B
(
b INT
);

-- Insertar datos
Insert into A (a) Values (1);
Insert into A (a) Values (2);
Insert into A (a) Values (3);
Insert into A (a) Values (4);
Insert into B (b) Values (3);
Insert into B (b) Values (4);
Insert into B (b) Values (5);
Insert into B (b) Values (6);
COMMIT;

-- Tabla A
SELECT * FROM A;

-- Tabla B
SELECT * FROM B;

/* Inner Join. */
-- Unión interna, filas que ambas tablas tienen en común.
select * from A INNER JOIN B on A.a = B.b;
select A.*, B.*  from A, B where A.a = B.b;

/* Left outer join */
-- Unión externa por la izquierda, todas las filas de A (tabla izquierda) relacionadas con B, así estas tengan o no coincidencias.
select * from A LEFT OUTER JOIN B on A.a = B.b;
select A.*,B.*  from A,B where A.a = B.b(+);

/* Right outer join */
-- Unión externa por la derecha, todas las filas de B (tabla derecha), así estas tengan o no coincidencias con A.
select * from A RIGHT OUTER JOIN B on A.a = B.b;
select A.*,B.*  from A,B where A.a(+) = B.b;

/* Full outer join */
-- Unión externa completa, unión externa por la izquierda unida a unión externa por la derecha. 

-- En oracle:
select * from A FULL OUTER JOIN B on A.a = B.b;

-- En MySql no está implementado FULL OUTER JOIN, para obtener este mismo resultado:

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;

Watch:

• Schema in oracle live
• Schema in sqlfiddle
• MySQL Documentation

My details for explanation:

CREATE TABLE Departamentos (
    Id int,
    Nombre varchar(20)
);

CREATE TABLE Empleados (
    Nombre varchar(20),
    DepartamentoId int
);

INSERT INTO Departamentos VALUES(31, 'Sales'),
                                (33, 'Engineering'),
                                (34, 'Clerical'),
                                (35, 'Marketing');

INSERT INTO Empleados VALUES('Rafferty', 31),
                            ('Jones', 33),
                            ('Heisenberg', 33),
                            ('Robinson', 34),
                            ('Smith', 34),
                            ('Williams', NULL);

Ejecutar

INNER JOIN Clause

This clause searches for a match between two tables, it is similar to the primary key table union that was used in the old days, for exampleSELECT * FROM Departamentos, Empleados WHERE Departamentos.Id=Empleados.DepartamentosId;

For example: If I want to list the employees and indicate the name of the department to which they belong, we can do the following:

SELECT * 
FROM Empleados E
JOIN Departamentos D
ON E.DepartamentoId = D.Id

Ejecutar

The result will be:

+----------+------------------+---------+----------------+
|Nombre    |   DepartmentoId  |   Id    |   Nombre       |
+----------+------------------+---------+----------------+
|Rafferty  |        31        |    31   |    Sales       |
|Jones     |        33        |    33   |    Engineering |
|Heisenberg|        33        |    33   |    Engineering |
|Robinson  |        34        |    34   |    Clerical    |
|Smith     |        34        |    34   |    Clerical    |
+----------+------------------+---------+----------------+

And from here we realize the following:

• The employee "Williams" does not appear in the results, as he does not belong to any department that currently exists.

• The "Marketing" department does not appear either, since no employee belongs to that department.

Why is this happening? Because it shows as a result the intersection of both tables.

Keep in mind that in the results we see 4 columns. The first 2 correspond to the Employees table and the last ones to Departments.

This happens because we are selecting all the columns with an Asterisk(*).

If we want, we can be specific and select only 2 columns:

SELECT E.Nombre as 'Empleado',
D.Nombre as 'Departamento'
FROM Empleados E
JOIN Departamentos D
ON E.DepartamentoId = D.Id

And get :

+-----------+-----------------+
| Empleado  |   Departamento  |
+-----------+-----------------+
| Rafferty  |    Sales        |
| Jones     |    Engineering  |
| Heisenberg|    Engineering  |
| Robinson  |    Clerical     |
| Smith     |    Clerical     |
+-----------+-----------------+

Ejecutar

LEFT JOIN Clause

Unlike an INNER JOIN with a LEFT JOIN we give priority to the left table, and search the right table.

If there is no match for any of the rows in the left table, all results from the first table are displayed anyway.

An example:

SELECT E.Nombre as 'Empleado', D.Nombre as 'Departamento'
FROM Empleados E
LEFT JOIN Departamentos D
ON E.DepartamentoId = D.Id

• The Employees table is the first table to appear in the query (in the FROM), so this is the LEFT table, and all of its rows will show up in the results.

• The Departments table is the table on the right (it appears after the LEFT JOIN). Therefore, if matches are found, the corresponding values ​​will be displayed, otherwise NULL will be displayed in the results.

+------------+---------------+
| Empleado   |  Departamento |
+------------+---------------+
| Rafferty   |  Sales        |
| Jones      |  Engineering  |
| Heisenberg |  Engineering  |
| Robinson   |  Clerical     |
| Smith      |  Clerical     |
| Williams   |  NULL         |
+------------+---------------+

Ejecutar

RIGHT JOIN Clause

In the case of RIGHT JOIN it is very similar, but here the table on the right is given priority.

So if I use the following query, we are displaying all rows from the table on the right:

SELECT E.Nombre as 'Empleado',D.Nombre as 'Departamento'
FROM Empleados E
RIGHT JOIN Departamentos D
ON E.DepartamentoId = D.Id

Ejecutar

The table on the left is Employees, while Departments is the table on the right.

The table associated with the FROM will always be the LEFT table, and the table that comes after the JOIN will be the RIGHT table.

So the result will show all departments at least 1 time.

If there are no employees working in a department, NULL will be displayed. But the department will show up anyway.

+------------+-----------------+
| Empleado   |  Departamento   |
+------------+-----------------+
| Rafferty   |   Sales         |
| Jones      |   Engineering   |
| Heisenberg |   Engineering   |
| Robinson   |   Clerical      |
| Smith      |   Clerical      |
| NULL       |   Marketing     |
+------------+-----------------+

FULL JOIN Clause

FULL JOIN is a mix between LEFT JOIN and RIGHT JOIN since both tables are displayed.

In the example, the following occurs:

SELECT E.Nombre as 'Empleado',D.Nombre as 'Departamento'
FROM Empleados E
FULL JOIN Departamentos D
ON E.DepartamentoId = D.Id

But unfortunately it is not yet implemented in MySQL, meanwhile we can join LEFT JOIN and RIGHT JOIN to obtain FULL JOIN. So a similar way to do it would be as follows:

SELECT E.Nombre as 'Empleado',
  D.Nombre as 'Departamento'
  FROM Empleados E
  LEFT JOIN Departamentos D
  ON E.DepartamentoId = D.Id
UNION
SELECT E.Nombre as 'Empleado',
  D.Nombre as 'Departamento'
  FROM Empleados E
  RIGHT JOIN Departamentos D
  ON E.DepartamentoId = D.Id;

The employee "Williams" is shown even though he is not assigned to any department, and the "Marketing" department is shown even though no one is working there yet:

+------------+----------------+
| Empleado   |  Departamento  |
+------------+----------------+
| Rafferty   |  Sales         |
| Jones      |  Engineering   |
| Heisenberg |  Engineering   |
| Robinson   |  Clerical      |
| Smith      |  Clerical      |
| Williams   |  NULL          |
| NULL       |  Marketing     |
+------------+----------------+

Ejecutar

More variants

If we pay attention to the Venn diagrams, we will notice that it is possible to form even more combinations, when selecting data.

For example, we have the following case, known as Left Excluding JOIN:

And similarly Right Excluding JOIN:

These combinations are possible to achieve if we add some conditions to our queries, making use of the WHERE clause.

For example, following the example we are looking at (where Employees is the left table and Departments is the right table):

• Left Excluding JOIN will allow us to obtain the list of employees who have not yet been assigned to any work department.

• While Right Excluding JOIN will show us the list of departments that do not have any associated workers.

Information taken from: https://programacionymas.com/blog/como-carga-inner-left-right-full-join