7 Composiciones

Entendemos por composición cualquier consulta multitabla. En SQL hay varios tipos de composiciones, aunque no todos están disponibles en SQLite.

Producto cartesiano

Consiste en combinar todas las filas de todas las tablas que intervienen en la operación. Si se componen dos tablas, T1 y T2, donde T1 tiene N1 filas y M1 columnas y T2 N2 filas y M2 columnas, el resultado será una tabla de N1*N2 filas y M1+M2 columnas.

Como estas operaciones se pueden asociar, si intervienen tres tablas, la tabla resultante tendrá N1*N2*N3 filas, y M1+M2+M3 columnas.

Borremos las tablas del ejemplo anterior y reconstruyámoslas de nuevo:

sqlite> DROP TABLE cancion;
sqlite> DROP TABLE artista;
sqlite> CREATE TABLE artista(
   ...> idartista INTEGER PRIMARY KEY,
   ...> nombre TEXT
   ...> );
sqlite> CREATE TABLE cancion(
   ...> idcancion INTEGER PRIMARY KEY,
   ...> titulo TEXT,
   ...> idartista INTEGER REFERENCES artista(idartista) ON UPDATE CASCADE ON DELETE SET NULL
   ...> );
sqlite> INSERT INTO artista (nombre) VALUES("Mike Oldfield");
sqlite> INSERT INTO artista (nombre) VALUES("The Beatles");
sqlite> INSERT INTO cancion (nombre,cancionartista) VALUES("Tubular Bells", 1);
Error: table cancion has no column named cancionartista
sqlite> INSERT INTO cancion (titulo,idartista) VALUES("Tubular Bells", 1);
sqlite> INSERT INTO cancion (titulo,idartista) VALUES("Yellow Submarine", 2);
sqlite> INSERT INTO cancion (titulo,idartista) VALUES("Get Back", 2);
sqlite> INSERT INTO cancion (titulo,idartista) VALUES("Yesterday", 2);
sqlite> INSERT INTO cancion (titulo,idartista) VALUES("Moonlight Shadow",1);
sqlite> SELECT * FROM artista,cancion;
idartista|nombre|idcancion|titulo|idartista
1|Mike Oldfield|1|Tubular Bells|1
1|Mike Oldfield|2|Yellow Submarine|2
1|Mike Oldfield|3|Get Back|2
1|Mike Oldfield|4|Yesterday|2
1|Mike Oldfield|5|Moonlight Shadow|1
2|The Beatles|1|Tubular Bells|1
2|The Beatles|2|Yellow Submarine|2
2|The Beatles|3|Get Back|2
2|The Beatles|4|Yesterday|2
2|The Beatles|5|Moonlight Shadow|1
sqlite>

Composiciones internas

Se denominan composiciones internas aquellas en las que no aparecen filas que no estén presentes en el producto cartesiano.

Generalmente, los productos cartesianos tienen una utiliad limitada. Lo normal es que se espeficifique alguna condición para eliminar algunas de las filas de ese producto, dejando sólo las que tengan sentido.

Aunque hay muchas opciones a la hora de elegir cláusulas para hacer composiciones, en realidad casi todas son equivalentes. JOIN, CROSS JOIN, INNER JOIN y la coma producen los mismos resultados. La única diferencia está en CROSS JOIN que mantiene el orden en que se componen las tablas, mientras que el resto de las cláusulas dejan al optimizador de código que elija el orden que produzca la salida más eficientemente.

Así, las siguientes consultas tienen la misma salida:

sqlite> SELECT * FROM artista,cancion;
sqlite> SELECT * FROM artista INNER JOIN cancion;
sqlite> SELECT * FROM artista CROSS JOIN cancion;
sqlite> SELECT * FROM artista JOIN cancion;

Limitar filas de composiciones

Disponemos de varias formas de especificar condiciones que eliminen filas del producto cartesiano.

Por supuesto, está la cláusula WHERE:

sqlite> SELECT * FROM artista,cancion WHERE artista.idartista=cancion.idartista;
idartista|nombre|idcancion|titulo|idartista
1|Mike Oldfield|1|Tubular Bells|1
2|The Beatles|2|Yellow Submarine|2
2|The Beatles|3|Get Back|2
2|The Beatles|4|Yesterday|2
1|Mike Oldfield|5|Moonlight Shadow|1
sqlite>

Pero esta forma tiene algunos inconvenientes.

• Se siguen generando tantas columnas como la suma de columnas de cada tabla, aunque evidentemente, en este caso las dos columnas 'idartista' son idénticas, y podría suprimirse una.
• Si necesitamos incluir más condiciones, no es evidente cuales de ellas definen la composición y cuales filtran los resultados. Esto es más bien una cuestión de estilo.
• Las cláusulas WHERE se evalúan para cada fila de la composición, al contrario de las que veremos ahora, que actúan antes, evitando que se generen filas que no cumplan la condición.

Las cláusulas ON y USING están orientadas específicamente a hacer composiciones.

La cláusula ON permite definir una condición, que puede ser cualquier expresión booleana:

sqlite> SELECT * FROM artista,cancion ON(artista.idartista=cancion.idartista);
idartista|nombre|idcancion|titulo|idartista
1|Mike Oldfield|1|Tubular Bells|1
2|The Beatles|2|Yellow Submarine|2
2|The Beatles|3|Get Back|2
2|The Beatles|4|Yesterday|2
1|Mike Oldfield|5|Moonlight Shadow|1
sqlite>

Vemos que la salida es la misma que con WHERE, y que la columna 'idartista' se muestra dos veces.

Si, como en este caso, hemos usado el mismo identificador para la clave foránea en ambas tablas, podemos hacer uso de la cláusula USING:

sqlite> SELECT * FROM artista,cancion USING(idartista);
idartista|nombre|idcancion|titulo
1|Mike Oldfield|1|Tubular Bells
2|The Beatles|2|Yellow Submarine
2|The Beatles|3|Get Back
2|The Beatles|4|Yesterday
1|Mike Oldfield|5|Moonlight Shadow
sqlite>

Ahora sólo se muestra una vez la columna 'idartista', aunque la tabla resultante tiene la misma información.

Este tipo de composición se conoce como composición natural, y es tan frecuente que disponemos de una cláusula especial para crear este tipo de composiciones, NATURAL JOIN:

sqlite> SELECT * FROM artista NATURAL JOIN cancion;
idartista|nombre|idcancion|titulo
1|Mike Oldfield|1|Tubular Bells
2|The Beatles|2|Yellow Submarine
2|The Beatles|3|Get Back
2|The Beatles|4|Yesterday
1|Mike Oldfield|5|Moonlight Shadow
sqlite>

Composiciones externas

En las composiciones externas sí pueden aparecer filas que no estarán presentes en un producto cartesiano.

Por ejemplo, en nuestra base de datos añadiremos un artista para el que no haya ninguna canción:

sqlite> INSERT INTO artista (nombre) VALUES("The Rolling Stones");
sqlite>

La salida del producto cartesiano es la esperada:

sqlite> SELECT * FROM artista,cancion;
idartista|nombre|idcancion|titulo|idartista
1|Mike Oldfield|1|Tubular Bells|1
1|Mike Oldfield|2|Yellow Submarine|2
1|Mike Oldfield|3|Get Back|2
1|Mike Oldfield|4|Yesterday|2
1|Mike Oldfield|5|Moonlight Shadow|1
2|The Beatles|1|Tubular Bells|1
2|The Beatles|2|Yellow Submarine|2
2|The Beatles|3|Get Back|2
2|The Beatles|4|Yesterday|2
2|The Beatles|5|Moonlight Shadow|1
3|The Rolling Stones|1|Tubular Bells|1
3|The Rolling Stones|2|Yellow Submarine|2
3|The Rolling Stones|3|Get Back|2
3|The Rolling Stones|4|Yesterday|2
3|The Rolling Stones|5|Moonlight Shadow|1
sqlite>

Si se usa LEFT JOIN o LEFT OUTER JOIN con la cláusula correspondiente ON o USING, se generará una fila de salida para cada fila de la tabla de entrada de la izquierda a la que no corresponda ninguna fila de la tabla de la derecha. Las columnas correspondientes a la tabla de la derecha toman el valor NULL:

sqlite> SELECT * FROM artista LEFT JOIN cancion USING(idartista);
idartista|nombre|idcancion|titulo
1|Mike Oldfield|5|Moonlight Shadow
1|Mike Oldfield|1|Tubular Bells
2|The Beatles|3|Get Back
2|The Beatles|2|Yellow Submarine
2|The Beatles|4|Yesterday
3|The Rolling Stones|NULL|NULL
sqlite>

Tambien podemos usar LEFT con composiciones naturales:

sqlite> SELECT * FROM artista NATURAL LEFT JOIN cancion;
idartista|nombre|idcancion|titulo
1|Mike Oldfield|5|Moonlight Shadow
1|Mike Oldfield|1|Tubular Bells
2|The Beatles|3|Get Back
2|The Beatles|2|Yellow Submarine
2|The Beatles|4|Yesterday
3|The Rolling Stones|NULL|NULL
sqlite>

SQLite no soporta composiciones externas a la derecha RIGHT JOIN o RIGHT OUTER JOIN.