1. Bases de dades relacionals

1.10. Consultes avançades: consultes multitaula

Per aprofitar al màxim el model relacional i generar nou coneixement de les dades existents resulta essencial combinar informació de diverses taules. Mitjançant un atribut que dues taules posseeixin en comú, aquesta operació resulta extremadament senzilla amb SQL. Mentre efectuem una consulta amb l’ordre SELECT, hem d’emprar la clàusula JOIN especificant l’atribut compartit per ambdues taules. Quan es referencien atributs de dues o més taules en la mateixa consulta, cal utilitzar la sintaxi nombre_taula.nom_ atribut per especificar clarament l’origen de cada atribut. És possible afegir-hi condicions sobre altres atributs de les taules amb la clàusula WHERE.

Donades dues taules que denominarem taula1 i taula2, que posseeixen un atribut comparable (taula1.x i taula2.y), la sintaxi de l’ordre JOIN per obtenir tant els valors en comú com els valors presents exclusivament en la primera o en la segona taula, respectivament, és la següent:

Abans de procedir a executar consultes sobre les taules de la base de dades, proposem posar a prova el funcionament de la clàusula JOIN sobre un exemple més simple.

Generarem dues taules que denominarem taula1 i taula2, amb un únic atribut. Posteriorment, poblarem ambdues taules amb una sèrie de valors tals que resultarà molt senzill mostrar el conjunt complet de combinacions a l’hora de comparar les dues taules.

Primer procedim a crear les dues taules:

Ara crearem dos fitxers de text senzills amb tres valors cadascun: (1,2,3) i (3,4,5). D’aquesta manera, podrem estudiar detalladament la classe de consulta de MySQL que necessitem per recuperar els valors comuns entre ambdues taules (3) o, alternativament, els valors que únicament apareixen a la primera (1 i 2) o a la segona taula (4 i 5).

Finalment, poblem les dues taules utilitzant ambdós fitxers:

Ja estem en disposició d’aprofundir en el funcionament de les consultes que inclouen la clàusula JOIN. Si no hi afegim cap opció, aquesta ordre genera totes les parelles possibles el primer element de les quals pertany a la primera taula i el segon element pertany a la segona:

El nostre primer objectiu en una comparació és localitzar els elements comuns. Per tal de fer-ho n’hi ha prou amb incorporar la clàusula ON a la consulta i especificar l’atribut compartit per les dues taules. Això filtrarà aquelles parelles del resultat anterior que no compleixin aquesta propietat, i es demostrarà allò que busquem:

En determinats casos, en lloc de la llista dels valors comuns, estarem interessats també en aquells valors d’una o altra taula que no pertanyen a l’altra. Per això hem de modificar el comportament de l’ordre JOIN amb les clàusules LEFT o RIGHT. Si realitzem una unió per la part esquerra, recuperarem un llistat dels registres de la primera taula juntament amb el seu registre equivalent a la segona. En el cas que aquest valor anàleg no existís, MySQL ho indicarà amb el valor NULL. Si, per contra, realitzem la unió per la dreta, obtindrem un llistat dels valors de la segona taula sota les mateixes condicions.

Per acabar de refinar el resultat, hem de mantenir exclusivament els valors que només estan en una taula. Per aconseguir-ho, podem afegir al final de la consulta una condició WHERE que exigeixi que el valor no estigui present a l’altra taula. La clàusula IS realitza l’avaluació de l’expressió que es troba a continuació, per acabar responent amb un valor booleà (cert o fals).

Ara ja estem en condicions de realitzar consultes sobre dues o més taules del nostre catàleg de gens. Per exemple, podem preguntar per aquells gens humans per als quals s’ha documentat una anotació funcional de caràcter experimental: