Intro a SQL y MySQL - Tema 11 - Join

11.1. Acercamiento a la necesidad de los JOIN

Sabemos enlazar varias tablas para mostrar datos que estén relacionados, empleando "WHERE". Por ejemplo, podríamos mostrar nombres de deportistas, junto con los nombres de los deportes que practican. Pero todavía hay un detalle que se nos escapa: ¿cómo hacemos si queremos mostrar todos los deportes que hay en nuestra base de datos, incluso aunque no haya deportistas que los practiquen?

Vamos a crear una base de datos sencilla para ver un ejemplo de cual es este problema y de cómo solucionarlo.

Nuestra base de datos se llamará "ejemploJoins":

create database ejemploJoins;
use ejemploJoins;

En ella vamos a crear una primera tabla en la que guardaremos "capacidades" de personas (cosas que saben hacer):

create table capacidad(
  codigo varchar(4),
  nombre varchar(20),
  primary key(codigo)
);

También crearemos una segunda tabla con datos básicos de personas:

create table persona(
  codigo varchar(4),
  nombre varchar(20),
  codcapac varchar(4),
  primary key(codigo)
);

Vamos a introducir datos de ejemplo:

insert into capacidad values
  ('c','Progr.C'),
  ('pas','Progr.Pascal'),
  ('j','Progr.Java'),
  ('sql','Bases datos SQL');
  
insert into persona values
  ('ju','Juan','c'),
  ('ja','Javier','pas'),
  ('jo','Jose','perl'),
  ('je','Jesus','html');

Antes de seguir, comprobamos que todo está bien:

select * from capacidad;

+--------+-----------------+
| codigo | nombre          |
+--------+-----------------+
| c      | Progr.C         |
| j      | Progr.Java      |
| pas    | Progr.Pascal    |
| sql    | Bases datos SQL |
+--------+-----------------+

select * from persona;

+--------+--------+----------+
| codigo | nombre | codcapac |
+--------+--------+----------+
| ja     | Javier | pas      |
| je     | Jesus  | html     |
| jo     | Jose   | perl     |
| ju     | Juan   | c        |
+--------+--------+----------+

Como se puede observar, hay dos capacidades en nuestra base de datos para las que no conocemos a ninguna persona; de igual modo, existen dos personas que tienen capacidades sobre las que no tenemos ningún detalle.

Por eso, si mostramos las personas con sus capacidades de la forma que sabemos, sólo aparecerán las parejas de persona y capacidad para las que todo está claro (existe persona y existe capacidad), es decir:

select * from capacidad, persona
where persona.codcapac = capacidad.codigo;

+--------+--------------+--------+--------+----------+
| codigo | nombre       | codigo | nombre | codcapac |
+--------+--------------+--------+--------+----------+
| c      | Progr.C      | ju     | Juan   | c        |
| pas    | Progr.Pascal | ja     | Javier | pas      |
+--------+--------------+--------+--------+----------+

Podemos resumir un poco esta consulta, para mostrar sólo los nombres, que son los datos que más nos interesan:

select persona.nombre, capacidad.nombre
from persona, capacidad
where persona.codcapac = capacidad.codigo;

+--------+--------------+
| nombre | nombre       |
+--------+--------------+
| Juan   | Progr.C      |
| Javier | Progr.Pascal |
+--------+--------------+

Hay que recordar que la orden "where" es obligatoria: si no indicamos esa condición, se mostraría el "producto cartesiano" de las dos tablas: todos los pares (persona, capacidad), aunque no estén relacionados en nuestra base de datos:

select persona.nombre, capacidad.nombre
from persona, capacidad;

+--------+-----------------+
| nombre | nombre          |
+--------+-----------------+
| Javier | Progr.C         |
| Jesus  | Progr.C         |
| Jose   | Progr.C         |
| Juan   | Progr.C         |
| Javier | Progr.Java      |
| Jesus  | Progr.Java      |
| Jose   | Progr.Java      |
| Juan   | Progr.Java      |
| Javier | Progr.Pascal    |
| Jesus  | Progr.Pascal    |
| Jose   | Progr.Pascal    |
| Juan   | Progr.Pascal    |
| Javier | Bases datos SQL |
| Jesus  | Bases datos SQL |
| Jose   | Bases datos SQL |
| Juan   | Bases datos SQL |
+--------+-----------------+

11.2. JOIN cruzados, internos y externos

Pues bien, con órdenes "join" podemos afinar cómo queremos enlazar (en inglés, "join", unir) las tablas. Por ejemplo, si queremos ver todas las personas y todas las capacidades, aunque no estén relacionadas, como en el ejemplo anterior, algo que no suele tener sentido en la práctica, lo podríamos hacer con un "cross join" (unir de forma cruzada):

select persona.nombre, capacidad.nombre
from persona cross join capacidad;

+--------+-----------------+
| nombre | nombre          |
+--------+-----------------+
| Javier | Progr.C         |
| Jesus  | Progr.C         |
| Jose   | Progr.C         |
| Juan   | Progr.C         |
| Javier | Progr.Java      |
| Jesus  | Progr.Java      |
| Jose   | Progr.Java      |
| Juan   | Progr.Java      |
| Javier | Progr.Pascal    |
| Jesus  | Progr.Pascal    |
| Jose   | Progr.Pascal    |
| Juan   | Progr.Pascal    |
| Javier | Bases datos SQL |
| Jesus  | Bases datos SQL |
| Jose   | Bases datos SQL |
| Juan   | Bases datos SQL |
+--------+-----------------+

Si sólo queremos ver los datos que coinciden en ambas tablas, lo que antes conseguíamos comparando los códigos con un "where", también podemos usar un "inner join" (unión interior; se puede abreviar simplemente "join"):

select persona.nombre, capacidad.nombre
from persona inner join capacidad
on persona.codcapac = capacidad.codigo;

+--------+--------------+
| nombre | nombre       |
+--------+--------------+
| Juan   | Progr.C      |
| Javier | Progr.Pascal |
+--------+--------------+

Pero aquí llega la novedad: si queremos ver todas las personas y sus capacidades (si existen), mostrando incluso aquellas personas para las cuales no tenemos constancia de ninguna capacidad, usaríamos un "left join" (unión por la izquierda, también se puede escribir "left outer join", unión exterior por la izquierda, para dejar claro que se van a incluir datos que están sólo en una de las dos tablas):

select persona.nombre, capacidad.nombre
from persona left outer join capacidad
on persona.codcapac = capacidad.codigo;

+--------+--------------+
| nombre | nombre       |
+--------+--------------+
| Juan   | Progr.C      |
| Javier | Progr.Pascal |
| Jesus  | NULL         |
| Jose   | NULL         |
+--------+--------------+

De igual modo, si queremos ver todas las capacidades, incluso aquellas para las que no hay detalles sobre personas, podemos escribir el orden de las tablas al revés en la consulta anterior, o bien usar "right join" (o "right outer join"):

select persona.nombre, capacidad.nombre
from persona right outer join capacidad
on persona.codcapac = capacidad.codigo;

+--------+-----------------+
| nombre | nombre          |
+--------+-----------------+
| Javier | Progr.Pascal    |
| Juan   | Progr.C         |
| NULL   | Progr.Java      |
| NULL   | Bases datos SQL |
+--------+-----------------+

El significado de "LEFT" y de "RIGHT" hay que buscarlo en la posición en la que se enumeran las tablas en el bloque "FROM". Así, la última consulta se puede escribir también como un LEFT JOIN si se indica la capacidad en segundo lugar (en la parte izquierda), así:

select persona.nombre, capacidad.nombre
from capacidad left outer join persona
on persona.codcapac = capacidad.codigo;

+--------+-----------------+
| nombre | nombre          |
+--------+-----------------+
| Javier | Progr.Pascal    |
| Juan   | Progr.C         |
| NULL   | Progr.Java      |
| NULL   | Bases datos SQL |
+--------+-----------------+

Otros gestores de bases de datos permiten combinar el "right join" y el "left join" en una única consulta, usando "full outer join", algo que no permite MySQL en su versión actual, pero que se puede imitar de una forma que veremos en el próximo apartado.

(Más detalles en el apartado 14.2.9.2 del manual de referencia MySQL 5.7).

11.3. Ejercicios propuestos

• 11.1. Crea una base de datos "ejercicio11", en la que guardaremos información sobre deportistas y disciplinas deportivas. Tendremos una tabla "DEPORTES" y una tabla "DEPORTISTAS", unidas por una relación M:M (cada deporte puede ser practicado por más de un deportista y cada deportista podría practicar más de un deporte). Además, existirá una tabla "PAISES". Para cada país guardaremos el nombre (por ejemplo, "España") y un código que actuará como clave primaria (por ejemplo, "ESP). De cada deportista anotaremos código, nombre, apellidos. Para cada deporte, nos interesará código y nombre. La relación M:M se reflejará en una nueva tabla "PRACTICAR", cada uno de cuyos registros estará formado por el código de deporte y el código de deportista (ambos formarán la clave primaria -compuesta- de esta nueva tabla). La relación 1:M entre deportista y país se reflejará en que de cada deportista deberá se deberá anotar también el código del país al que representa.
• 11.2a. Añade los países:
  • JAM, Jamaica
  • ESP, España
  • USA, Estados Unidos de América
  • AUS, Australia
  • RUS, Rusia
• 11.2b. Añade los deportistas:
  • BOL, Usain, Bolt (Jamaica)
  • POW, Asafa, Powell (Jamaica)
  • CRA, Saúl, Craviotto (España)
  • TAU, Diana, Taurasi (Estados Unidos)
  • PHE, Michael, Phelps (Estados Unidos)
  • MUR, Andy, Murray (UK)
• 11.2c. Añade los deportes:
  • ATL, Atletismo
  • REM, Remo
  • BAL, Baloncesto
  • NAT, Natación
  • BAD, Badminton
• 11.2d. Y la relación Practicar:
  • Usain Bolt, Velocidad
  • Saúl Craviotto, Remo
  • Diana Taurasi, Baloncesto
  • Michael Phelps, Natación
• 11.3. Muestra los nombres y apellidos de todos los deportistas, junto al nombre del país al que pertenecen.
• 11.4. Muestra los nombres y apellidos de todos los deportistas, junto al nombre del país al que pertenecen, en caso de que dicho país aparezca en la base de datos. También se deben mostrar los datos de los deportistas cuyo país no se haya introducido aún.
• 11.5. Muestra los nombres de todos los países, junto a los apellidos de los deportistas de ese país. Deben aparecer también los países de los que no conozcamos ningún deportista.
• 11.6. Muestra los nombres y apellidos de todos los deportistas, junto al nombre del deporte que practican (sólo aquellos de los que tengamos constancia que realmente practican algún deporte).
• 11.7. Muestra los nombres y apellidos de todos los deportistas, junto al nombre del deporte que practican (incluso los que no sepamos cuál es su deporte).
• 11.8. Muestra el nombre de todos los deportes, junto con los nombres y apellidos de los deportistas que lo practican (incluso si para algún deporte aún no hemos introducido ningún deportista).