1. Bases de datos relacionales

1.10. Consultas avanzadas: consultas multitabla

Para aprovechar al máximo el modelo relacional y generar nuevo conocimiento de los datos existentes resulta esencial combinar información de varias tablas. Mediante un atributo que dos tablas posean en común, esta operación resulta extremadamente sencilla con SQL. Mientras efectuamos una consulta con el comando SELECT, debemos emplear la cláusula JOIN especificando el atributo compartido por ambas tablas. Cuando se referencian atributos de dos o más tablas en la misma consulta, es necesario utilizar la sintaxis nombre_tabla.nombre_atributo para especificar claramente el origen de cada atributo. Es posible añadir condiciones sobre otros atributos de las tablas con la cláusula WHERE.

Dadas dos tablas que denominaremos tabla1 y tabla2, que poseen un atributo comparable (tabla1.x y tabla2.y), la sintaxis del comando JOIN para obtener tanto los valores en común como los valores presentes exclusivamente en la primera o en la segunda tabla, respectivamente, es la siguiente:

SELECT tabla1.x,tabla2.y
FROM tabla1 JOIN tabla2
ON tabla1.x=tabla2.y
WHERE condiciones;
----------------------------------------
SELECT tabla1.x,tabla2.y
FROM tabla1 LEFT JOIN tabla2
ON tabla1.x=tabla2.y
WHERE condiciones;
----------------------------------------
SELECT tabla1.x,tabla2.y
FROM tabla1 RIGHT JOIN tabla2
ON tabla1.x=tabla2.y
WHERE condiciones;

Antes de proceder a ejecutar consultas sobre las tablas de la base de datos catalogo, proponemos poner a prueba el funcionamiento de la cláusula JOIN sobre un ejemplo más simple.

Vamos a generar dos tablas que denominaremos tabla1 y tabla2, con un único atributo. Posteriormente, poblaremos ambas tablas con una serie de valores tales que resultará muy sencillo mostrar el conjunto completo de combinaciones a la hora de comparar las dos tablas.

Primero procedemos a crear las dos tablas:

mysql> CREATE TABLE tabla1
    -> (x VARCHAR(10));

Query OK, 0 rows affected (0.5 sec)

mysql> CREATE TABLE tabla2
     -> (y VARCHAR(10));

Query OK, 0 rows affected (0.5 sec)

Ahora vamos a crear dos sencillos ficheros de texto con tres valores cada uno: (1,2,3) y (3,4,5). De este modo, vamos a poder estudiar detalladamente la clase de consulta de MySQL que necesitamos para recuperar los valores comunes entre ambas tablas (3) o, alternativamente, los valores que únicamente aparecen en la primera (1 y 2) o en la segunda tabla (4 y 5).

1
2
3

---------------------------------------------

3
4
5

Finalmente, poblamos las dos tablas utilizando ambos ficheros:

mysql> LOAD DATA LOCAL INFILE 'datos1.txt' INTO TABLE tabla1;

Query OK, 3 rows affected (0.67 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> LOAD DATA LOCAL INFILE 'datos2.txt' INTO TABLE tabla2;

Query OK, 3 rows affected (0.43 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Ya estamos en disposición de profundizar en el funcionamiento de las consultas que incluyen la cláusula JOIN. Si no añadimos ninguna opción, este comando genera todas las parejas posibles cuyo primer elemento pertenece a la primera tabla y el segundo elemento pertenece a la segunda:

mysql> SELECT tabla1.x,tabla2.y
    -> FROM tabla1 JOIN tabla2;

+-----+-----+
| x   | y   |
+-----+-----+
| 1   | 3   |
| 2   | 3   |
| 3   | 3   |
| 1   | 4   |
| 2   | 4   |
| 3   | 4   |
| 1   | 5   |
| 2   | 5   |
| 3   | 5   |
+-----+-----+
9 rows in set (0.00 sec)

Nuestro primer objetivo en una comparación es localizar los elementos comunes. Para ello es suficiente con incorporar la cláusula ON a la consulta y especificar el atributo compartido por las dos tablas. Esto filtrará aquellas parejas del resultado anterior que no cumplan esta propiedad, mostrándose aquello que buscábamos:

mysql> SELECT tabla1.x,tabla2.y
    -> FROM tabla1 JOIN tabla2
    -> ON tabla1.x=tabla2.y;

+-----+-----+
| x   | y   |
+-----+-----+
| 3   | 3   |
+-----+-----+
1 row in set (0.00 sec)

En determinados casos, en lugar de la lista de los valores comunes, estaremos interesados también en aquellos valores de una u otra tabla que no pertenecen a la otra. Para ello debemos modificar el comportamiento del comando JOIN con las cláusulas LEFT o RIGHT. Si realizamos una unión por la parte izquierda, recuperaremos un listado de los registros de la primera tabla junto con su registro equivalente en la segunda. En el caso de que este valor análogo no existiera, MySQL lo indicará con el valor NULL. Si, por el contrario, realizamos la unión por la derecha, obtendremos un listado de los valores de la segunda tabla bajo las mismas condiciones.

mysql> SELECT tabla1.x,tabla2.y
    -> FROM tabla1 LEFT JOIN tabla2
    -> ON tabla1.x=tabla2.y;

+-----+------+
| x   | y    |
+-----+------+
| 3   | 3    |
| 1   | NULL |
| 2   | NULL |
+-----+------+
3 rows in set (0.00 sec)

mysql> SELECT tabla1.x,tabla2.y
    -> FROM tabla1 RIGHT JOIN tabla2
    -> ON tabla1.x=tabla2.y;

+------+-----+
| x    | y   |
+------+-----+
| 3    | 3   |
| NULL | 4   |
| NULL | 5   |
+------+-----+
3 rows in set (0.00 sec)

Para acabar de refinar el resultado, debemos mantener exclusivamente los valores que solo están en una tabla. Para lograrlo, podemos añadir al final de la consulta una condición WHERE que exija que el valor no esté presente en la otra tabla. La cláusula IS realiza la evaluación de la expresión que se encuentra a continuación, para acabar respondiendo con un valor booleano (cierto o falso).

mysql> SELECT tabla1.x,tabla2.y
    -> FROM tabla1 LEFT JOIN tabla2
    -> ON tabla1.x=tabla2.y
    -> WHERE tabla2.y IS NULL;

+-----+------+
| x   | y    |
+-----+------+
| 1   | NULL |
| 2   | NULL |
+-----+------+
2 rows in set (0.00 sec)

mysql> SELECT tabla1.x,tabla2.y
    -> FROM tabla1 RIGHT JOIN tabla2
    -> ON tabla1.x=tabla2.y
    -> WHERE tabla1.x IS NULL;

+------+-----+
| x    | y   |
+------+-----+
| NULL | 4   |
| NULL | 5   |
+------+-----+
2 rows in set (0.00 sec)

Ahora ya estamos en condiciones de efectuar consultas sobre dos o más tablas de nuestro catálogo de genes. Por ejemplo, podemos preguntar por aquellos genes humanos para los cuales se ha documentado una anotación funcional de carácter experimental:

mysql> SELECT genes.nombre,genes.especie,
    -> anotaciones.funcion,anotaciones.origen
    -> FROM genes JOIN anotaciones
    -> ON genes.nombre=anotaciones.nombre
    -> WHERE anotaciones.origen='experimental'
    -> AND genes.especie='H. sapiens';

+--------+------------+------------+--------------+
| nombre |   especie  |  funcion   |    origen    |
+--------+------------+------------+--------------+
| HNF1A  | H. sapiens | GO:0003700 | Experimental |
| MYC    | H. sapiens | GO:0003700 | Experimental |
+--------+------------+------------+--------------+
2 rows in set (0.00 sec)