Guía ilustrada de las uniones múltiples

Hasta ahora, nuestros artículos de la serie "Una Guía Ilustrada" han explicado varios tipos de join: INNER JOINs, OUTER JOINs (LEFT JOIN, RIGHT JOIN, FULL JOIN), CROSS JOIN, self-join y non-equi join. En este último artículo de la serie, le mostramos cómo crear consultas SQL que hagan coincidir datos de varias tablas utilizando uno o varios tipos de join.

Tipos de unión en las consultas SQL

Antes de empezar a hablar de ejemplos de consultas SQL que utilizan tipos de join múltiples, vamos a hacer un breve resumen de los tipos de join que hemos cubierto hasta ahora, para asegurarnos de que entiendes las diferencias. Para ello, aquí tenemos un breve resumen en forma de tabla. Echa un vistazo:

Type of JOIN	Matching records from tables	Explanation
INNER JOIN(JOIN) SELECT t.id, t.size, t.color_id, c.id, c.color FROM tshirt t JOIN color c ON t.color_id = c.id ;	Result: id size color_id id color 2 M 1 1 yellow 3 NULL 3 3 blue	An INNER JOIN returns records that match in both tables.
LEFT JOIN SELECT t.id, t.size, t.color_id, c.id, c.color FROM tshirt t LEFT JOIN color c ON t.color_id = c.id ;	Result: id size color_id id color 2 M 1 1 yellow 3 NULL 3 3 blue 1 S NULL NULL NULL	A LEFT JOIN returns all records from the left table, even when they do not match in the right table. Missing values become NULLs.
RIGHT JOIN SELECT t.id, t.size, t.color_id, c.id, c.color FROM tshirt t RIGHT JOIN color c ON t.color_id = c.id ;	Result: id size color_id id color 2 M 1 1 yellow 3 NULL 3 3 blue NULL NULL NULL 2 NULL	A RIGHT JOIN returns all records from the right table, even when they do not match those in the left table. Missing values become NULLs.
FULL JOIN SELECT t.id, t.size, t.color_id, c.id, c.color FROM tshirt t FULL JOIN color c ON t.color_id = c.id ;	Result: id size color_id id color 2 M 1 1 yellow 3 NULL 3 3 blue 1 S NULL NULL NULL NULL NULL NULL 2 NULL	A FULL JOIN returns all records from both tables: left and right, even when rows do not match. Missing values become NULLs.
CROSS JOIN SELECT t.id, t.size, t.color_id, c.id, c.color FROM tshirt t CROSS JOIN color c ;	Result: id size color_id id color 2 M 1 1 yellow 2 M 1 2 NULL 2 M 1 3 blue 3 NULL 3 1 yellow 3 NULL 3 2 NULL 3 NULL 3 3 blue 1 S NULL 1 yellow 1 S NULL 2 NULL 1 S NULL 3 blue	A CROSS JOIN returns the Cartesian product of the records from both tables. This means that each record from the left table is joined with each record from the right table. Missing values become NULLs.

Tenga en cuenta que el resultado de cada tipo de join contiene datos que provienen de las tablas tshirt y color. El tipo de join específico determina el contenido de la tabla a devolver.

¿Qué es un join múltiple en SQL?

Cada consulta puede comprender cero, uno o más joins. Un join múltiple es el uso de más de un join en una sola consulta. Los joins utilizados pueden ser todos del mismo tipo, o sus tipos pueden ser diferentes. Comenzaremos nuestra discusión mostrando una consulta de ejemplo que utiliza dos uniones del mismo tipo. Observe la siguiente consulta.

SELECT v.name, c.name,  p.lastname
FROM vehicle v
INNER JOIN color c ON  v.color_id = c.id
INNER JOIN person p ON v.person_id = p.id ;

La consulta invoca dos INNER JOINs para unir tres tablas: vehicle, person y color. Sólo se devolverán los registros que coincidan en cada tabla. En primer lugar, observe los conjuntos de datos que se han unido.

Hay tres conjuntos de datos que corresponden a tres tablas de la base de datos: vehicle, color y person, representados a continuación.

person

color

vehicle

Puede ver que cada vehicle en la tabla de vehículos tiene un color, excepto motorcycle. Cada vehículo tiene un propietario asignado, excepto bicycle, que no tiene propietario. Uno de los colores (blue) de la tabla color no está asignado a ningún vehículo. Además, la motocicleta no tiene un color disponible en la base de datos. Por otro lado, bicycle tiene un color asignado, pero no tiene dueño. Por último, la persona llamada Miller no tiene asignado ningún vehículo.

En la consulta anterior hemos utilizado la unión múltiple para recuperar sólo los vehículos que tienen asignado un color y un propietario. La tabla vehicle tiene la columna color_id que identifica el color en la tabla de colores, así como la columna person_id que identifica al person en la tabla de personas.

Resultado de la consulta:

Resulta que sólo dos registros coinciden con los criterios definidos por las dos uniones internas.

La siguiente imagen presenta la secuencia en la que se han unido los registros de las respectivas tablas.

Observe que todas las operaciones de JOIN se realizan de izquierda a derecha. En el primer paso, se cotejan las tablas de la primera JOIN (tablas vehicle y color). Como resultado, se crea una tabla intermedia. En el segundo paso, esta tabla intermedia (tratada como la tabla de la izquierda) se une a otra tabla (tabla person) utilizando el segundo JOIN.

Recuerde que una única JOIN de cualquier tipo produce una única tabla intermedia (comúnmente llamada tabla derivada) durante una consulta multijoin.

Unión mixta izquierda y derecha con unión interna

También es posible combinar diferentes tipos de uniones en una consulta multi-join. Tomemos un ejemplo con INNER JOIN y LEFT JOIN. Supongamos que queremos consultar en nuestra base de datos todas las personas que tienen un vehículo de color o que no tienen ningún vehículo.

Intuitivamente, empezaríamos con la tabla person y la uniríamos con la tabla vehicle tabla utilizando un LEFT JOIN. En ese caso, el LEFT JOIN haría coincidir cada registro de la person con un registro de la tabla vehicle y, en el caso de las personas para las que no se encontrara un registro coincidente, rellenaría los valores que faltaran con NULLs. Esta unión producirá una lista de todas las personas de la base de datos con datos de vehículos asociados, aunque no posean uno. Pero a nosotros nos interesa ver sólo los vehículos con colores asignados. Esto significa que debemos utilizar un INNER JOIN en las tablas vehicle y color. Aquí tenemos una consulta que cumple ese requisito concreto, pero ¿hace el trabajo?

SELECT v.name vehicle_name, c.name color_name,  p.lastname
FROM person p
LEFT JOIN vehicle v ON  v.person_id = p.id
INNER JOIN color c ON v.color_id = c.id ;

Esta consulta nos devuelve el mismo resultado que obtuvimos con nuestra consulta anterior (que sólo utilizaba INNER JOINs). Nuestra lista no incluye a los que no tienen vehículo.

Resultado:

¿Pero qué ha pasado? El INNER JOIN omitió los resultados que no coincidían en ambas tablas, es decir, en la tabla derivada (creada al unir las tablas person y vehicle) y en la tabla color tabla. ¿Cómo podemos resolver este problema?

La siguiente consulta presenta una de las posibles soluciones. En este caso, la tabla derivada devuelve sólo los vehículos con colores, y luego se RIGHT JOINed con la tabla person para obtener todas las personas.

SELECT p.lastname, v.name, c.name
FROM vehicle v
INNER JOIN color c ON  v.color_id = c.id
RIGHT JOIN person p ON v.person_id = p.id ;

Resultado:

Ahora tenemos una lista de todas las personas: las que tienen vehículos de color y las que no tienen vehículos. Empezamos con una INNER JOIN de tablas vehicle y color. Cada vehículo incluido en la tabla derivada debe tener un color asignado, por lo que este tipo de unión es apropiado. Una vez seleccionados los vehículos de color, podríamos utilizar un RIGHT JOIN en la tabla derivada con la tabla person tabla, que es como obtuvimos las personas que no eran propietarias de vehículos junto a las que (de la tabla derivada) eran propietarias de un vehículo de color.

Otro método para resolver este problema es utilizar un LEFT JOIN sobre la tabla de personas y una subconsulta en la que utilizamos un INNER JOIN sobre las tablas vehicle y color.

Observe la siguiente consulta.

SELECT p.lastname, o.vehicle_name, o.color_name
FROM person p LEFT JOIN
(  SELECT v.name vehicle_name, c.name color_name, v.person_id
    FROM vehicle v
    INNER JOIN color c ON v.color_id=c.id
) o ON  o.person_id = p.id;

Mixed JOINs con Full JOIN

Otro tipo de multijoin utiliza joins completos. Primero, echemos un vistazo a una unión múltiple con uniones completas solamente.

SELECT p.lastname, v.name, c.name
FROM vehicle v
FULL JOIN color c ON  v.color_id = c.id
FULL JOIN person p ON v.person_id = p.id ;

La consulta anterior hace coincidir los registros de tres tablas: person, vehicle y color de tal manera que incluso los registros que no coincidan con las otras dos tablas aparecerán en la tabla de resultados. Las columnas vacías se rellenarán con los valores de NULL. Por ello, la consulta devuelve todas las personas independientemente de si tienen un vehículo, todos los vehículos independientemente de si tienen un color asignado, y todos los colores independientemente de si están asignados a algún vehículo.

Resultado:

Hemos utilizado las uniones completas para unir todos los registros, incluso los que no coinciden. Recuerde que las uniones completas devuelven todos los registros, mientras que las uniones internas sólo devuelven los que coinciden.

La siguiente imagen explica la secuencia en la que se unieron las tablas.

FULL JOIN también puede aparecer en una consulta con otro tipo de unión, creando así una unión múltiple con tipos mixtos. La siguiente consulta utiliza un FULL JOIN con un INNER JOIN.

SELECT p.lastname, v.name, c.name
FROM vehicle v
INNER  JOIN color c ON  v.color_id = c.id
FULL  JOIN person p ON v.person_id = p.id ;

Esta consulta nos permite recuperar una lista de todas las personas, sean o no propietarias de vehículos, y de todos los vehículos que tienen un color asignado.

Así es como funcionan las dos uniones:

En primer lugar, las tablas vehicle y color se combinan utilizando un INNER JOIN. A continuación, la tabla derivada se combina con la tabla person utilizando un FULL JOIN. Este es el resultado:

Resumen

Una sola consulta SQL puede unir dos o más tablas. Cuando hay tres o más tablas involucradas, las consultas pueden usar un solo tipo de unión más de una vez, o pueden usar múltiples tipos de unión. Cuando se utilizan varios tipos de unión, debemos considerar cuidadosamente la secuencia de unión para producir el resultado deseado. Los ejemplos presentados en este artículo demuestran claramente cómo un pequeño cambio en el tipo de unión (o, en el caso de las uniones múltiples, el orden en el que aparecen en la consulta) puede cambiar completamente el resultado de la consulta, haciendo o deshaciendo el éxito de la misma.

¿A qué combinaciones de join debemos prestar especial atención? INNER JOINs con OUTER JOINs, y OUTER JOINs con OUTER JOINs. Cada una de estas combinaciones puede producir resultados de consulta erróneos cuando se utilizan de forma inapropiada.

Más información sobre SQL

El conocimiento básico de las uniones SQL es una necesidad absoluta, pero la mayoría de los principiantes de SQL se sienten intimidados por las declaraciones JOIN. La verdad es que no hay nada que temer.

En este artículo, hemos hablado de cómo utilizar las uniones múltiples en una sola consulta: ya sea LIKE o mixta JOIN tipos. Puedes encontrar más información sobre joins en el material completo de SQL de la Academia Vertabelo. En los cursos, aumentará en gran medida su experiencia, probando y perfeccionando sus nuevas habilidades a través de los ejercicios de práctica interactivos proporcionados. Comienza por el curso SQL para principiantes si no tienes conocimientos previos de SQL. Vaya al curso de SQL JOINs para ganar práctica en la consulta de múltiples tablas a través de toneladas de ejercicios interactivos en las declaraciones de JOIN. ¡Pruébalo ahora de forma gratuita!