Combinación externa OUTER JOIN

La combinación interna solo conserva las filas para las que se ha encontrado coincidencia en la segunda tabla. La combinación externa funciona de otra manera: devuelve siempre todas las filas de una de las tablas o de las dos, y la mitad que falta la rellena con valores NULL.

Hay tres tipos de combinación externa: izquierda (LEFT), derecha (RIGHT) y completa (FULL). Es obligatorio indicar el tipo: escribir solo OUTER JOIN no está permitido y da un error de sintaxis. En cambio, la palabra OUTER es opcional: LEFT JOIN y LEFT OUTER JOIN significan lo mismo, y a partir de aquí usaremos la forma corta.

Combinación externa izquierda (LEFT OUTER JOIN)

Devuelve todas las filas de la tabla izquierda. Si una fila tiene coincidencia en la tabla derecha, las dos filas se unen; si no la tiene, las columnas de la tabla derecha se rellenan con NULL.

Como ejemplo, vamos a obtener de la base de datos el horario de timbres combinado con las clases correspondientes del horario de clases.

Datos de la tabla Timepair (horario de timbres):

Datos de la tabla Schedule (horario de clases):

MySQL 8.4
SELECT Timepair.id "timepair.id", start_pair, end_pair,
    Schedule.id "schedule.id", date, class, number_pair, teacher, subject, classroom
FROM Timepair
    LEFT JOIN Schedule ON Schedule.number_pair = Timepair.id;

En el resultado aparecen los ocho timbres, tal y como promete la combinación izquierda. Pero han salido 47 filas, no 8: 44 filas para las clases de Schedule y otras 3 para los timbres sin clases.

La combinación no se limita a completar la tabla izquierda: recorre todos los pares de filas que cumplen la condición. Un mismo número de hora de clase aparece muchas veces en el horario de clases (en días distintos y en grupos distintos), y cada coincidencia genera una fila aparte. Si la clave de la tabla derecha no es única, el resultado tendrá más filas que la tabla izquierda.

Al final del resultado hay filas en las que todas las columnas de la clase son NULL. Son los timbres para los que no se ha encontrado ninguna clase: no tienen coincidencia, pero la fila de la tabla izquierda tiene que aparecer igualmente en el resultado.

Filas sin coincidencia

Sobre estos NULL se apoya el truco práctico más habitual: encontrar los registros que no tienen coincidencia. Basta con quedarse solo con las filas en las que la clave de la tabla derecha está vacía:

MySQL 8.4
SELECT Timepair.id, start_pair, end_pair
FROM Timepair
    LEFT JOIN Schedule ON Schedule.number_pair = Timepair.id
WHERE Schedule.number_pair IS NULL;

Quedan tres timbres a los que no se ha asignado ninguna clase.

Una combinación de la que solo se conservan las filas sin coincidencia se llama anti-join (ANTI JOIN). No tiene sintaxis propia: tanto en MySQL como en PostgreSQL se escribe justo así, con una combinación y la condición IS NULL.

Combinación externa derecha (RIGHT OUTER JOIN)

Es el reflejo de la izquierda: en el resultado entran obligatoriamente todas las filas de la tabla derecha, y las columnas que faltan de la izquierda se rellenan con NULL.

MySQL 8.4
SELECT Timepair.id "timepair.id", start_pair, end_pair,
    Schedule.id "schedule.id", date, class, number_pair, teacher, subject, classroom
FROM Timepair
    RIGHT JOIN Schedule ON Schedule.number_pair = Timepair.id;

El resultado tiene 44 filas, exactamente tantas como registros hay en Schedule, y ninguna fila con NULL. Es decir, coincide por completo con la combinación interna.

Esto ocurre porque cada clase hace referencia a un timbre que existe: en la tabla derecha simplemente no hay filas sin coincidencia. El tipo de combinación fija la regla, pero lo que acaba en el resultado lo deciden los datos.

Combinación externa completa (FULL OUTER JOIN)

Devuelve todas las filas de ambas tablas. Las filas que tienen coincidencia se unen, y las filas sin coincidencia de la tabla izquierda y de la derecha entran en el resultado con NULL en lugar de la otra mitad.

El resultado de una combinación completa se compone de tres partes:

• las filas que entran en la combinación interna (INNER JOIN);
• las filas de la tabla izquierda que no tienen coincidencia;
• las filas de la tabla derecha que no tienen coincidencia.

MySQL 8.4
SELECT Timepair.id "timepair.id", start_pair, end_pair,
    Schedule.id "schedule.id", date, class, number_pair, teacher, subject, classroom
FROM Timepair
    FULL OUTER JOIN Schedule ON Schedule.number_pair = Timepair.id;

SELECT campos_tablas
FROM tabla_izquierda
    LEFT JOIN tabla_derecha ON tabla_derecha.clave = tabla_izquierda.clave

UNION ALL

SELECT campos_tablas
FROM tabla_izquierda
    RIGHT JOIN tabla_derecha ON tabla_derecha.clave = tabla_izquierda.clave
WHERE tabla_izquierda.clave IS NULL;

Todos los tipos de combinación de tablas

SELECT campos_tablas
FROM tabla_izquierda
    LEFT JOIN tabla_derecha ON tabla_derecha.clave = tabla_izquierda.clave

Ponte a prueba. La tabla izquierda tiene 8 filas. ¿Cuántas filas devolverá un LEFT JOIN con la tabla derecha?