¿Cuál es la diferencia entre un inner y un outer join?

Question

¿Cuál es la diferencia entre un inner y un outer join?

Y ¿cuál es la función de los modificadores?

• left
• right
• full

Answer 1

Asumiendo que se está haciendo un join de columnas sin duplicados, lo cuál es un caso común:

• Un inner join de A y B entregará el resultado de la intersección de los conjuntos A y B. En otras palabras, la parte interna –intersección– en un diagrama de Venn.

• Un full outer join entre A y B entregará el resultado de la unión de A y B. En otras palabras, la parte externa –unión– en un diagrama de Venn .

Ejemplos:

Supongamos que tenemos dos tablas, con una sola columna cada una y los siguientes datos:

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

Nota que (1,2) solo se encuentran en A, (3,4) son comunes y (5,6) solamente se encuentran en B.

Inner Join

Un inner join –utilizando cualquiera de las sintaxis de consulta equivalente– te entrega la intersección de ambas tablas, es decir, las 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;

a | b
--+--
3 | 3
4 | 4

Left outer join

Un outer join por la izquierda, te dará todas las filas de A, incluyendo las filas comunes entre A y 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

Un outer join por la derecha te dará todas las filas de B, incluyendo las filas comunes 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;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Full outer join

Un outer join completo (full) te entregará la unión de A y B; es decir, todas las filas de A y todas las filas de B. Si una fila en A no tiene una fila correspondiente en B, la porción de B es null, y 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

Diagramas de Venn

Esto mismo, podemos verlo con diagramas de Venn:

Imagen SQL Joins.svg de Arbeck, compartida bajo licencia CC BY 3.0.

Answer 2

Voy a utilizar el mismo ejemplo de jachguate el cual es muy claro agregando unos pequeños detalles.

Glosario

• inner join (traducción libre: Unión interna)
• outer join (traducción libre: Unión externa)

-- 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;

Ver:

• Esquema en oracle live
• Esquema en sqlfiddle
• Documentación MySql

Answer 3

Mis datos para la explicación :

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

Cláusula INNER JOIN

Esta clausula busca coincidencia entre dos tablas, es parecido a la union de tabla de clave primaria que se usaba antiguamente como por ejemplo SELECT * FROM Departamentos, Empleados WHERE Departamentos.Id=Empleados.DepartamentosId;

Por ejemplo : Si quiero listar los empleados y indicar el nombre del departamento al que pertenecen pues podemos realizar lo siguiente :

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

Ejecutar

El resultado será :

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

Y a partir de aquí nos damos cuentas delo siguiente:

• El empleado "Williams" no aparece en los resultados, ya que no pertenece a ningún departamento que actualmente exista.

• El departamento "Marketing" tampoco aparece, ya que no hay ningún empleado pertenece a dicho departamento.

¿Por qué ocurre esto? Porque muestra como resultado la intersección de ambas tablas.

Hay que tener en cuenta que, en los resultados vemos 4 columnas. Las 2 primeras se corresponden con la tabla Empleados y las últimas con Departamentos.

Esto ocurre porque estamos seleccionando todas las columnas con un Asterisco(*).

Si queremos, podemos ser específicos y seleccionar sólo 2 columnas :

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

Y obtener :

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

Ejecutar

Cláusula LEFT JOIN

A diferencia de un INNER JOIN con LEFT JOIN damos prioridad a la tabla de la izquierda, y buscamos en la tabla derecha.

Si no existe ninguna coincidencia para alguna de las filas de la tabla de la izquierda, de igual forma todos los resultados de la primera tabla se muestran.

Un ejemplo:

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

• La tabla Empleados es la primera tabla en aparecer en la consulta (en el FROM), por lo tanto ésta es la tabla LEFT (izquierda), y todas sus filas se mostrarán en los resultados.

• La tabla Departamentos es la tabla de la derecha (aparece luego del LEFT JOIN). Por lo tanto, si se encuentran coincidencias, se mostrarán los valores correspondientes, pero sino, aparecerá NULL en los resultados.

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

Ejecutar

Cláusula RIGHT JOIN

En el caso de RIGHT JOIN es muy similar, pero aquí se da prioridad a la tabla de la derecha.

De tal modo que si uso la siguiente consulta, estamos mostrando todas las filas de la tabla de la derecha:

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

Ejecutar

La tabla de la izquierda es Empleados, mientras que Departamentos es la tabla de la derecha.

La tabla asociada al FROM será siempre la tabla LEFT, y la tabla que viene después del JOIN será la tabla RIGHT.

Entonces el resultado mostrará todos los departamentos al menos 1 vez.

Si no hay ningún empleado trabajando en un departamento, se mostrará NULL. Pero el departamento aparecerá de todo modos.

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

Cláusula FULL JOIN

FULL JOIN es una mezcla entre LEFT JOIN y RIGHT JOIN ya que se muestra ambas tablas.

En el ejemplo, ocurre lo siguiente:

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

Pero todavía no esta implementado desgraciadamente en MySQL, mientras tanto podemos unir LEFT JOIN y RIGHT JOIN para obtener FULL JOIN. Por lo que una manera similar de realizarlo seria de la siguiente forma :

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;

Se muestra el empleado "Williams" a pesar que no está asignado a ningún departamento, y se muestra el departamento de "Marketing" a pesar que aún nadie está trabajando allí :

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

Ejecutar

Más variantes

Si prestamos atención a los diagramas de Venn, vamos a notar que es posible formar incluso más combinaciones, al momento de seleccionar datos.

Por ejemplo, tenemos el siguiente caso, conocido como Left Excluding JOIN:

Y de igual manera Right Excluding JOIN:

Estas combinaciones son posibles de lograr si añadimos algunas condiciones a nuestras consultas, haciendo uso de la cláusula WHERE.

Por ejemplo, siguiendo el ejemplo que estamos viendo (donde Empleados es la tabla izquierda y Departamentos la tabla derecha):

• Left Excluding JOIN nos permitirá obtener la lista de empleados que aún no han sido asignados a ningún departamento de trabajo.

• Mientras que Right Excluding JOIN nos mostrará la lista de departamentos que no tienen a ningún trabajador asociado.

Información sacada de : https://programacionymas.com/blog/como-funciona-inner-left-right-full-join