12 Lenguaje SQL: Consultas multitabla

Hasta ahora todas las consultas que hemos usado se refieren sólo a una tabla, pero también es posible hacer consultas usando varias tablas en la misma sentencia SELECT.

Esto nos permite realizar otras dos operaciones de álgebra relacional que aún no hemos visto: el producto cartesiano y la composición.

Producto cartesiano

Usaremos el ejemplo de las tablas de personas2 y telefonos2 del capítulo 7, e insertaremos algunos datos:

mysql> INSERT INTO personas2 (nombre, fecha) VALUES
    -> ("Fulanito", "1956-12-14"),
    -> ("Menganito", "1975-10-15"),
    -> ("Tulanita", "1985-03-17"),
    -> ("Fusganita", "1976-08-25");
Query OK, 4 rows affected (0.09 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM personas2;
+----+-----------+------------+
| id | nombre    | fecha      |
+----+-----------+------------+
|  1 | Fulanito  | 1956-12-14 |
|  2 | Menganito | 1975-10-15 |
|  3 | Tulanita  | 1985-03-17 |
|  4 | Fusganita | 1976-08-25 |
+----+-----------+------------+
4 rows in set (0.00 sec)

mysql>

Ahora insertaremos datos en la tabla de telefonos2:

mysql> INSERT INTO telefonos2 (id, numero) VALUES
    -> (1, "123456789"),
    -> (1, "145654854"),
    -> (1, "152452545"),
    -> (2, "254254254"),
    -> (4, "456545654"),
    -> (4, "441415414");
Query OK, 6 rows affected (0.06 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM telefonos2;
+-----------+---------+
| numero    | persona |
+-----------+---------+
| 123456789 |       1 |
| 145654854 |       1 |
| 152452545 |       1 |
| 254254254 |       2 |
| 456545654 |       4 |
| 441415414 |       4 |
+-----------+---------+
6 rows in set (0.00 sec)

mysql>

El producto cartesiano de dos tablas son todas las combinaciones de todas las filas de las dos tablas. Usando una sentencia SELECT se hace proyectando todos los atributos de ambas tablas. Los nombres de las tablas se indican en la cláusula FROM separados con comas:

mysql> SELECT * FROM personas2,telefonos2;
+----+-----------+------------+-----------+----+
| id | nombre    | fecha      | numero    | id |
+----+-----------+------------+-----------+----+
|  1 | Fulanito  | 1956-12-14 | 123456789 |  1 |
|  2 | Menganito | 1975-10-15 | 123456789 |  1 |
|  3 | Tulanita  | 1985-03-17 | 123456789 |  1 |
|  4 | Fusganita | 1976-08-25 | 123456789 |  1 |
|  1 | Fulanito  | 1956-12-14 | 145654854 |  1 |
|  2 | Menganito | 1975-10-15 | 145654854 |  1 |
|  3 | Tulanita  | 1985-03-17 | 145654854 |  1 |
|  4 | Fusganita | 1976-08-25 | 145654854 |  1 |
|  1 | Fulanito  | 1956-12-14 | 152452545 |  1 |
|  2 | Menganito | 1975-10-15 | 152452545 |  1 |
|  3 | Tulanita  | 1985-03-17 | 152452545 |  1 |
|  4 | Fusganita | 1976-08-25 | 152452545 |  1 |
|  1 | Fulanito  | 1956-12-14 | 254254254 |  2 |
|  2 | Menganito | 1975-10-15 | 254254254 |  2 |
|  3 | Tulanita  | 1985-03-17 | 254254254 |  2 |
|  4 | Fusganita | 1976-08-25 | 254254254 |  2 |
|  1 | Fulanito  | 1956-12-14 | 456545654 |  4 |
|  2 | Menganito | 1975-10-15 | 456545654 |  4 |
|  3 | Tulanita  | 1985-03-17 | 456545654 |  4 |
|  4 | Fusganita | 1976-08-25 | 456545654 |  4 |
|  1 | Fulanito  | 1956-12-14 | 441415414 |  4 |
|  2 | Menganito | 1975-10-15 | 441415414 |  4 |
|  3 | Tulanita  | 1985-03-17 | 441415414 |  4 |
|  4 | Fusganita | 1976-08-25 | 441415414 |  4 |
+----+-----------+------------+-----------+----+
24 rows in set (0.73 sec)

mysql>

Como se ve, la salida consiste en todas las combinaciones de todas las tuplas de ambas tablas.

Composición (Join)

Recordemos que se trata de un producto cartesiano restringido, las tuplas que se emparejan deben cumplir una determinada condición.

En el álgebra relacional sólo hemos hablado de composiciones en general. Sin embargo, en SQL se trabaja con varios tipos de composiciones.

Composiciones internas

Todas las composiciones que hemos visto hasta ahora se denominan composiciones internas. Para hacer una composición interna se parte de un producto cartesiano y se eliminan aquellas tuplas que no cumplen la condición de la composición.

En el ejemplo anterior tenemos 24 tuplas procedentes del producto cartesiano de las tablas personas2 y teléfonos2. Si la condición para la composición es que personas2.id=telefonos2.id, tendremos que eliminar todas las tuplas en que la condición no se cumpla.

Estas composiciones se denominan internas porque en la salida no aparece ninguna tupla que no esté presente en el producto cartesiano, es decir, la composición se hace en el interior del producto cartesiano de las tablas.

Para consultar la sintaxis de las composiciones ver JOIN.

Las composiciones internas usan estas sintaxis:

referencia_tabla, referencia_tabla
referencia_tabla [INNER | CROSS] JOIN referencia_tabla [condición]

La condición puede ser:

ON expresión_condicional | USING (lista_columnas)

La coma y JOIN son equivalentes, y las palabras INNER y CROSS son opcionales.

La condición en la cláusula ON puede ser cualquier expresión válida para una cláusula WHERE, de hecho, en la mayoría de los casos, son equivalentes.

La cláusula USING nos permite usar una lista de atributos que deben ser iguales en las dos tablas a componer.

Siguiendo con el mismo ejemplo, la condición más lógica para la composición interna entre personas2 y teléfonos2 es la igualdad entre el identificador de persona en la primera tabla y el atributo persona en la segunda:

mysql> SELECT * FROM personas2, telefonos2
    -> WHERE personas2.id=telefonos2.id;
+----+-----------+------------+-----------+----+
| id | nombre    | fecha      | numero    | id |
+----+-----------+------------+-----------+----+
|  1 | Fulanito  | 1956-12-14 | 123456789 |  1 |
|  1 | Fulanito  | 1956-12-14 | 145654854 |  1 |
|  1 | Fulanito  | 1956-12-14 | 152452545 |  1 |
|  2 | Menganito | 1975-10-15 | 254254254 |  2 |
|  4 | Fusganita | 1976-08-25 | 456545654 |  4 |
|  4 | Fusganita | 1976-08-25 | 441415414 |  4 |
+----+-----------+------------+-----------+----+
6 rows in set (0.73 sec)

mysql>

Esta consulta es equivalente a estas otras:

mysql> SELECT * FROM personas2 JOIN telefonos2
    -> ON (personas2.id = telefonos2.id);
mysql> SELECT * FROM personas2 JOIN telefonos2
    -> WHERE (personas2.id = telefonos2.id);
mysql> SELECT * FROM personas2 INNER JOIN telefonos2
    -> ON (personas2.id = telefonos2.id);
mysql> SELECT * FROM personas2 CROSS JOIN telefonos2
    -> ON (personas2.id = telefonos2.id);
mysql> SELECT * FROM personas2 JOIN telefonos2 USING(id);

En cualquier caso, la salida sólo contiene las tuplas que emparejan a personas con sus números de teléfono. Las tuplas correspondientes a personas que no tienen ningún número no aparecen, como por ejemplo las correspondientes a "Tulanita". Para las personas con varios números, se repiten los datos de la persona para cada número, por ejemplo con "Fulanito" o "Fusganita".

Composición interna natural

Consiste en una proyección sobre un producto cartesiano restringido. Es decir, sólo elegimos determinadas columnas de ambas tablas, en lugar de seleccionar todas.

Podemos hacer esto a partir de una composición general, eligiendo todas las columnas menos las repetidas:

mysql> SELECT personas2.id,nombre,fecha,numero
    -> FROM personas2, telefonos2
    -> WHERE personas2.id=telefonos2.id;
+----+-----------+------------+-----------+
| id | nombre    | fecha      | numero    |
+----+-----------+------------+-----------+
|  1 | Fulanito  | 1956-12-14 | 123456789 |
|  1 | Fulanito  | 1956-12-14 | 145654854 |
|  1 | Fulanito  | 1956-12-14 | 152452545 |
|  2 | Menganito | 1975-10-15 | 254254254 |
|  4 | Fusganita | 1976-08-25 | 456545654 |
|  4 | Fusganita | 1976-08-25 | 441415414 |
+----+-----------+------------+-----------+
6 rows in set (0.00 sec)

mysql>

Como la columna id existe en ambas tablas estamos obligados a usar el nombre completo para esta columna. En este caso hemos optado por personas2.id, pero hubiese sido igual usar telefonos2.id.

También podemos definir alias para las tablas, y conseguir una consulta más compacta:

mysql> SELECT t1.id,nombre,fecha,numero
    -> FROM personas2 AS t1, telefonos2 AS t2
    -> WHERE t1.id=t2.id;

Por supuesto, podemos usar JOIN y ON en lugar de la coma y WHERE:

mysql> SELECT t1.id,nombre,fecha,numero
    -> FROM personas2 AS t1 JOIN telefonos2 AS t2
    -> ON t1.id=t2.id;

Pero tenemos una sintaxis alternativa mucho mejor para hacer composiciones internas naturales:

referencia_tabla NATURAL JOIN referencia_tabla

Por ejemplo:

mysql> SELECT * FROM personas2 NATURAL JOIN telefonos2;
+----+-----------+------------+-----------+
| id | nombre    | fecha      | numero    |
+----+-----------+------------+-----------+
|  1 | Fulanito  | 1956-12-14 | 123456789 |
|  1 | Fulanito  | 1956-12-14 | 145654854 |
|  1 | Fulanito  | 1956-12-14 | 152452545 |
|  2 | Menganito | 1975-10-15 | 254254254 |
|  4 | Fusganita | 1976-08-25 | 456545654 |
|  4 | Fusganita | 1976-08-25 | 441415414 |
+----+-----------+------------+-----------+
6 rows in set (0.02 sec)

mysql>

Composiciones externas

Al contrario que con las composiciones internas, las externas no proceden de un producto cartesiano. Por lo tanto, en estas pueden aparecer tuplas que no aparecen en el producto cartesiano.

Para hacer una composición externa se toman las tuplas de una de las tablas una a una y se combinan con las tuplas de la otra.

Como norma general se usa un índice para localizar las tuplas de la segunda tabla que cumplen la condición, y para cada tupla encontrada se añade una fila a la tabla de salida.

Si no existe ninguna tupla en la segunda tabla que cumpla las condiciones, se combina la tupla de la primera con una nula de la segunda.

En nuestro ejemplo se tomaría la primera tupla de personas2, con un valor de id igual a 1, y se busca en la tabla telefonos2 las tuplas con un valor de id igual a 1. Lo mismo para la segunda tupla, con id igual a 2.

En la tercera el id es 3, y no existe ninguna tupla en telefonos2 con un valor de id igual a 3, por lo tanto se combina la tupla de personas2 con una tupla de telefonos2 con todos los atributos igual a NULL.

Por ejemplo:

mysql> SELECT * FROM personas2 LEFT JOIN telefonos2 USING(id);
+----+-----------+------------+-----------+------+
| id | nombre    | fecha      | numero    | id   |
+----+-----------+------------+-----------+------+
|  1 | Fulanito  | 1956-12-14 | 123456789 |    1 |
|  1 | Fulanito  | 1956-12-14 | 145654854 |    1 |
|  1 | Fulanito  | 1956-12-14 | 152452545 |    1 |
|  2 | Menganito | 1975-10-15 | 254254254 |    2 |
|  3 | Tulanita  | 1985-03-17 | NULL      | NULL | (1)
|  4 | Fusganita | 1976-08-25 | 456545654 |    4 |
|  4 | Fusganita | 1976-08-25 | 441415414 |    4 |
+----+-----------+------------+-----------+------+
7 rows in set (0.05 sec)

mysql>

La quinta fila (1), tiene valores NULL para numero e id de telefonos2, ya que no existen tuplas en esa tabla con un valor de id igual a 3.

Las sintaxis para composiciones externas son:

referencia_tabla LEFT [OUTER] JOIN referencia_tabla [join_condition]
referencia_tabla NATURAL LEFT [OUTER] JOIN referencia_tabla
referencia_tabla RIGHT [OUTER] JOIN referencia_tabla [condición]
referencia_tabla NATURAL RIGHT [OUTER] JOIN referencia_tabla

La condición puede ser:

ON expresión_condicional | USING (lista_columnas)

La palabra OUTER es opcional.

Existen dos grupos de composiciones externas: izquierda y derecha, dependiendo de cual de las tablas se lea en primer lugar.

Composición externa izquierda

En estas composiciones se recorre la tabla de la izquierda y se buscan tuplas en la de la derecha. Se crean usando la palabra LEFT (izquierda, en inglés).

Las sintaxis para la composición externa izquierda es:

referencia_tabla LEFT [OUTER] JOIN referencia_tabla [condición]

Veamos un ejemplo. Para empezar, crearemos un par de tablas:

mysql> CREATE TABLE tabla1 (
    -> id INT NOT NULL,
    -> nombre CHAR(10),
    -> PRIMARY KEY (id));
Query OK, 0 rows affected (0.42 sec)

mysql> CREATE TABLE tabla2 (
    -> id INT NOT NULL,
    -> numero INT,
    -> PRIMARY KEY (id));
Query OK, 0 rows affected (0.11 sec)

mysql>

E insertaremos algunos datos:

mysql> INSERT INTO tabla1 VALUES
    -> (5, "Juan"),
    -> (6, "Pedro"),
    -> (7, "José"),
    -> (8, "Fernando");
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO tabla2 VALUES
    -> (3, 30),
    -> (4, 40),
    -> (5, 50),
    -> (6, 60);
Query OK, 5 rows affected (0.05 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>

La composición izquierda sería:

mysql> SELECT * FROM tabla1 LEFT JOIN tabla2 USING(id);
+----+----------+------+--------+
| id | nombre   | id   | numero |
+----+----------+------+--------+
|  5 | Juan     |    5 |     50 |
|  6 | Pedro    |    6 |     60 |
|  7 | José     | NULL |   NULL |
|  8 | Fernando | NULL |   NULL |
+----+----------+------+--------+
4 rows in set (0.00 sec)

mysql>

Se puede ver que aparecen dos filas con valores NULL, para los id 7 y 8.

En contraposición, una composición interna dará esta salida:

mysql> SELECT * FROM tabla1 JOIN tabla2 USING(id);
+----+--------+----+--------+
| id | nombre | id | numero |
+----+--------+----+--------+
|  5 | Juan   |  5 |     50 |
|  6 | Pedro  |  6 |     60 |
+----+--------+----+--------+
2 rows in set (0.06 sec)

mysql>

Composición externa derecha

En este caso se recorre la tabla de la derecha y se buscan tuplas que cumplan la condición en la tabla izquierda.

La sintaxis es equivalente:

referencia_tabla LEFT [OUTER] JOIN referencia_tabla [condición]

Usando las mismas tablas que en el ejemplo anterior:

mysql> SELECT * FROM tabla1 RIGHT JOIN tabla2 USING(id);
+------+--------+----+--------+
| id   | nombre | id | numero |
+------+--------+----+--------+
| NULL | NULL   |  3 |     30 |
| NULL | NULL   |  4 |     40 |
|    5 | Juan   |  5 |     50 |
|    6 | Pedro  |  6 |     60 |
+------+--------+----+--------+
4 rows in set (0.00 sec)

mysql>

Es lo mismo usar una composición derecha de las tablas tabla1 y tabla2 que una composición izquierda de las tablas tabla2 y tabla1. Es decir, la consulta anterior es equivalente a esta otra:

mysql> SELECT * FROM tabla2 LEFT JOIN tabla1 USING(id);

Composiciones naturales externas

Por supuesto, también podemos hacer composiciones externas naturales:

referencia_tabla NATURAL LEFT [OUTER] JOIN referencia_tabla
referencia_tabla NATURAL RIGHT [OUTER] JOIN referencia_tabla

El problema es que si existen tuplas añadidas con respecto a la composición interna, no se eliminará ninguna columna. Los mismos ejemplos anteriores, como composiciones naturales externas serían:

mysql> SELECT * FROM tabla1 NATURAL LEFT JOIN tabla2;
+----+----------+------+--------+
| id | nombre   | id   | numero |
+----+----------+------+--------+
|  5 | Juan     |    5 |     50 |
|  6 | Pedro    |    6 |     60 |
|  7 | José     | NULL |   NULL |
|  8 | Fernando | NULL |   NULL |
+----+----------+------+--------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM tabla1 NATURAL RIGHT JOIN tabla2;
+------+--------+----+--------+
| id   | nombre | id | numero |
+------+--------+----+--------+
| NULL | NULL   |  3 |     30 |
| NULL | NULL   |  4 |     40 |
|    5 | Juan   |  5 |     50 |
|    6 | Pedro  |  6 |     60 |
+------+--------+----+--------+
4 rows in set (0.00 sec)

mysql>

Uniones

También es posible realizar la operación de álgebra relacional unión entre varias tablas o proyecciones de tablas.

Para hacerlo se usa la sentencia UNION que permite combinar varias sentencias SELECT para crear una única tabla de salida.

Las condiciones para que se pueda crear una unión son las mismas que vimos al estudiar el álgebra relacional: las relaciones a unir deben tener el mismo número de atributos, y además deben ser de dominios compatibles.

Veamos un ejemplo:

mysql> CREATE TABLE stock1 (
    -> id INT NOT NULL,
    -> nombre VARCHAR(30),
    -> cantidad INT,
    -> PRIMARY KEY (id));
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE stock2 (
    -> id INT NOT NULL,
    -> nombre VARCHAR(40),
    -> cantidad SMALLINT,
    -> PRIMARY KEY (id));
Query OK, 0 rows affected (0.16 sec)

mysql> CREATE TABLE stock3 (
    -> id INT NOT NULL,
    -> nombre VARCHAR(35),
    -> numero MEDIUMINT,
    -> PRIMARY KEY (id));
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO stock1 VALUES
    -> (1, "tornillo M3x12", 100),
    -> (2, "tornillo M3x15", 120),
    -> (3, "tornillo M4x25", 120),
    -> (4, "tornillo M5x30", 200);
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO stock2 VALUES
    -> (10, "tuerca M4", 120),
    -> (11, "tuerca M3", 100),
    -> (12, "tuerca M5", 87);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO stock3 VALUES
    -> (20, "varilla 10", 23),
    -> (1, "tornillo M3x12", 22),
    -> (21, "varilla 12", 32),
    -> (11, "tuerca M3", 22);
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>

Podemos crear una unión de las tres tablas, a pesar de que los nombres y tamaños de algunas columnas sean diferentes:

mysql> SELECT * FROM stock1 UNION
    -> SELECT * FROM stock2 UNION
    -> SELECT * FROM stock3;
+----+----------------+----------+
| id | nombre         | cantidad |
+----+----------------+----------+
|  1 | tornillo M3x12 |      100 |
|  2 | tornillo M3x15 |      120 |
|  3 | tornillo M4x25 |      120 |
|  4 | tornillo M5x30 |      200 |
| 10 | tuerca M4      |      120 |
| 11 | tuerca M3      |      100 |
| 12 | tuerca M5      |       87 |
|  1 | tornillo M3x12 |       22 |
| 11 | tuerca M3      |       22 |
| 20 | varilla 10     |       23 |
| 21 | varilla 12     |       32 |
+----+----------------+----------+
11 rows in set (0.00 sec)

mysql>

El resultado se puede ordenar usando ORDER BY y también podemos seleccionar un número limitado de filas mediante LIMIT:

mysql> (SELECT * FROM stock1) UNION
    -> (SELECT * FROM stock2) UNION
    -> (SELECT * FROM stock3) ORDER BY id LIMIT 6;
+----+----------------+----------+
| id | nombre         | cantidad |
+----+----------------+----------+
|  1 | tornillo M3x12 |      100 |
|  1 | tornillo M3x12 |       22 |
|  2 | tornillo M3x15 |      120 |
|  3 | tornillo M4x25 |      120 |
|  4 | tornillo M5x30 |      200 |
| 10 | tuerca M4      |      120 |
+----+----------------+----------+
6 rows in set (0.00 sec)

mysql>

Dentro de cada sentencia SELECT se aplican todas las cláusulas, proyecciones y selecciones que se quiera, como en cualquier SELECT normal.

La sintaxis completa incluye dos modificadores:

SELECT ...
UNION [ALL | DISTINCT]
SELECT ...
  [UNION [ALL | DISTINCT]
   SELECT ...]

Los modificadores ALL y DISTINCT son opcionales, y si no se usa ninguno el comportamiento es el mismo que si se usa DISTINCT.

Con ALL se muestran todas las filas, aunque estén repetidas, con DISTINCT sólo se muestra una copia de cada fila:

mysql> SELECT id,nombre FROM stock1 UNION
    -> SELECT id,nombre FROM stock2 UNION
    -> SELECT id,nombre FROM stock3;
+----+----------------+
| id | nombre         |
+----+----------------+
|  1 | tornillo M3x12 |
|  2 | tornillo M3x15 |
|  3 | tornillo M4x25 |
|  4 | tornillo M5x30 |
| 10 | tuerca M4      |
| 11 | tuerca M3      |
| 12 | tuerca M5      |
| 20 | varilla 10     |
| 21 | varilla 12     |
+----+----------------+
9 rows in set (0.00 sec)

mysql> SELECT id,nombre FROM stock1 UNION ALL
    -> SELECT id,nombre FROM stock2 UNION ALL
    -> SELECT id,nombre FROM stock3;
+----+----------------+
| id | nombre         |
+----+----------------+
|  1 | tornillo M3x12 |
|  2 | tornillo M3x15 |
|  3 | tornillo M4x25 |
|  4 | tornillo M5x30 |
| 10 | tuerca M4      |
| 11 | tuerca M3      |
| 12 | tuerca M5      |
|  1 | tornillo M3x12 |
| 11 | tuerca M3      |
| 20 | varilla 10     |
| 21 | varilla 12     |
+----+----------------+
11 rows in set (0.00 sec)

mysql>

Sobre el resultado final no se pueden aplicar otras cláusulas como GROUP BY.