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.
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.
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".
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 detelefonos2 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.
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>
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);
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>
No hay comentarios:
Publicar un comentario