Inicio‎ > ‎SQL‎ > ‎Lliçons SQL‎ > ‎

T03 Reunió (join)

L’ús d’unes quantes taules en una consulta, i la seua concatenació seguint qualsevol criteri, es coneix habitualment com join, el terme en anglès adoptat i utilitzat. Estem parlant, per exemple, de:
 

select *
from asignaturas, profesores, imparte
where profesores.dni = imparte.dni and asignatura = codigo


Tipus de reunió

Depenent de quines taules s’especifiquen en el from i del tipus de condició exigida per a relacionar les files d’aquestes taules, el join rep distints noms (terminologia que es pot considerar estàndard):

Equijoin

Consultes que comporten l’ús d’igualtats per a la concatenació de files de diverses taules. L’exemple anterior és una equijoin.

Self join

Aquestes consultes concatenen una taula amb si mateixa:

select i1.dni, ' imparte la misma asignatura que ', i2.dni
from imparte i1, imparte i2
where i1.asignatura= i2.asignatura

dni imparte la misma asignatura que dni
21111222 imparte la misma asignatura que 21111222
21111222 imparte la misma asignatura que 21111222
21333444 imparte la misma asignatura que 21333444

NOTA: en realidad, el ejemplo no es muy bueno porque, obviamente, un profesor da lo mismo que él mismo. Más claro, la orden coherente sería la siguiente.

select i1.dni, ' imparte la misma asignatura que ', i2.dni
from imparte i1, imparte i2
where i1.asignatura= i2.asignatura
  and i1.dni <> i2.dni

Pero, dado el contenido de la base de datos, la consulta no obtiene ninguna fila porque ningún profesor está compartiendo la asignatura con un compañero.

Producte cartesià

El producte cartesià utilitza dues taules sense la condició de concatenació del where:

select dni, codigo
from profesores, asignaturas

dni codigo
21111222 DGBD
21222333 DGBD
21333444 DGBD
21111222 FBD
21222333 FBD
21333444 FBD
21111222 FP
21222333 FP
21333444 FP
21111222 HI
21222333 HI
21333444 HI
21111222 PC
21222333 PC
21333444 PC

 

Inner join, simple join

Realment, es tracta de la consulta habitual en SQL. No obstant, hi ha una sintaxi particular alternativa usant inner join:

select nombre, descripcion
from asignaturas
            join imparte on (codigo=asignatura)
            join profesores on (imparte.dni=profesores.dni)
nombredescripcion
EVA GOMEZDISEÑO Y GESTION DE BASES DE DATOS
EVA GOMEZFUNDAMENTOS DE LAS BASES DE DATOS
RAFAEL ROMEROPROGRAMACION CONCURRENTE


El resultat serà la concatenació de totes aquelles files, i únicament aquestes, que compleixen la condició que les relaciona. És una construcció alternativa a què hem utilitzat fins ara que ens ha de ser familiar:

select nombre, descripcion
from asignaturas, profesores, imparte
where profesores.dni = imparte.dni and asignatura = codigo

Si de cas, pot tenir una certa utilitat per a no oblidar-nos d’enllaçar cada parell de taules i deixar el where per a un altre tipus de condicions:

select nombre, descripcion
from asignaturas join imparte on (codigo=asignatura)
join profesores on (imparte.dni=profesores.dni)
where descripcion NOT LIKE 'PROGRAMACION%'

nombredescripcion
EVA GOMEZDISEÑO Y GESTION DE BASES DE DATOS
EVA GOMEZFUNDAMENTOS DE LAS BASES DE DATOS

Outer join

El outer join es diferencia del inner join que les files d’una taula que es mostren en el resultat no necessàriament tenen la seua corresponent fila o files en l’altra taula.

Per exemple, podríem voler obtenir tots els professors i, si dóna alguna assignatura, el codi d’aquesta assignatura:
 
select p.*, i.asignatura
from profesores p
left join imparte i on (p.dni=i.dni);

dni nombre categoria ingreso asignatura
21111222 EVA GOMEZ TEU 1993-10-01 DGBD
21111222 EVA GOMEZ TEU 1993-10-01 FBD
21222333 MANUEL PALOMAR TEU 1989-06-16  null
21333444 RAFAEL ROMERO ASO6 1992-06-16 PC


Més avant s’aprofundeix en el outer join.

Antijoin

Aquestes consultes són les que utilitzen el NOT IN per a obtenir aquelles files d’una taula que no es relacionen amb les d’una altra:

select * from profesores
where dni not in (select dni from imparte);

dni nombre categoria ingreso
21222333 MANUEL PALOMAR TEU 1989-06-16

 

Semijoin

Un semijoin torna files que fan certa una subconsulta d’un operador EXISTS sense duplicar.

select * from profesores p
where exists (select * from imparte i where i.dni = p.dni);
dni nombre categoria ingreso
21111222 EVA GOMEZ TEU 1993-10-01
21333444 RAFAEL ROMERO ASO6 1992-06-16


L’operador exists i aquest tipus de subconsultes es veuran en sessions més avançades.

Donem-nos compte que, pràcticament, el que hem fet és donar nom als distints tipus de consultes, algunes ja utilitzades durant el curs. No és important aquest nom sinó entendre les necessitats de cada consulta i com satisfer-la.

Sí que és nova, per a aquest curs, la sintaxi del inner i el outer join. El primer, no cal desenrotllar-lo més, el segon sí el tractem en la següent secció.

Tinga’s en compte, també, que aquest no és un curs exhaustiu de SQL. Hi ha detalls de rendiment que afavoreixen l’ús d’uns o altres tipus de consultes i, per descomptat, moltes més opcions a l’hora d’incrementar aquest rendiment sobretot en entorns de mitjanes o grans bases de dades.

Per a més informació, podeu consultar Oracle® Database SQL Reference 10g Release 2 (10.2), que ha sigut la font d’aquesta sessió.


Outer join

Com ja hem dit, el outer join estèn el resultat d’una consulta simple de, per exemple, dues taules, obtenint totes les files que compleixen la condició de concatenació i, a més, totes o algunes de les files d’una taula que no compleixen tal condició.

Suposem dues taules A i B:

select * from A left [outer] join B on (condició)
Obté totes les files relacionades de A i B, i totes les no relacionades de A.

select * from A right [outer] join B on (condició)
Obté totes les files relacionades de A i B, i totes les no relacionades de B.

select * from A full [outer] join B on (condició)
(No suportat per MySQL) Obté totes les files relacionades de A i B, i totes les no relacionades de A i B.



BD Ejemplo

PROFESORES ( dni varchar2(10), nombre varchar(40), categoria char(4), ingreso date )
CP (dni)

ASIGNATURAS ( codigo char(5), descripcion varchar(35), creditos decimal(3,1), creditosp decimal(3,1) )
CP (código)

IMPARTE ( dni varchar(10), asignatura char(5) )
CP (dni, asignatura)
CAj (dni) → PROFESORES
CAj (asignatura) → ASIGNATURAS

COORDINADORES ( dni varchar(10), nombre varchar(40), dpto char(4), asig char(5) )
CP (dni)
CAj (asig) → ASIGNATURAS

Per a veure millor el funcionament de les distintes alternatives de join, treballarem amb una taula addicional, COORDINADORES, en la nostra base de dades Ejemplo.


Mostra tots els coordinadors i, si ho fan, les assignatures que coordinen.

select * from coordinadores left join asignaturas on (codigo=asig);

dni nombre dpto asig codigo descripcion creditos creditosp
55777666 AGAPITO CIFUENTES DLSI FP FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5
66555444 ROMUALDO GOMEZ DLSI HI HI HISTORIA DE LA INFORMATICA 4.5  null
99222111 CATURLO PEREZ DLSI  null  null  null  null  null


Mostra els coordinadors que tenen assignatura i totes les assignatures.

select * from coordinadores right join asignaturas on (codigo=asig);

dni nombre dpto asig codigo descripcion creditos creditosp
null null null null DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0
 null  null  null  null FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5
55777666 AGAPITO CIFUENTES DLSI FP FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5
66555444 ROMUALDO GOMEZ DLSI HI HI HISTORIA DE LA INFORMATICA 4.5  null
 null  null  null  null PC PROGRAMACION CONCURRENTE 6.0 1.5


Mostra tots els coordinadors i totes les assignatures i si hi ha relació entre ells.

select * from coordinadores full join asignaturas on (codigo=asig);

El que s’espera d’un full join és que apareguen totes les dades d’una i una altra taula, estiguen o no relacionats, més o menys, el que es mostra a continuació:

dni nombre dpto asig codigo descripcion creditos creditosp
null null null null DGBD DISEÑO Y GESTION DE BASES DE DATOS 6.0 3.0
55777666 AGAPITO CIFUENTES DLSI FP FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5
66555444 ROMUALDO GOMEZ DLSI HI HI HISTORIA DE LA INFORMATICA 4.5  null
 null  null  null  null FBD FUNDAMENTOS DE LAS BASES DE DATOS 6.0 1.5
 null  null  null  null PC PROGRAMACION CONCURRENTE 6.0 1.5
99222111 CATURLO PEREZ DLSI  null  null  null  null  null


No obstant això, full join no està suportat per MySQL encara que sí per altres motors (Oracle PL/SQL) i si executàrem la sentència anterior el resultat és idèntic a un join simple.

dni nombre dpto asig codigo descripcion creditos creditosp
55777666 AGAPITO CIFUENTES DLSI FP FP FUNDAMENTOS DE LA PROGRAMACION 9.0 4.5
66555444 ROMUALDO GOMEZ DLSI HI HI HISTORIA DE LA INFORMATICA 4.5  null