Inicio‎ > ‎SQL‎ > ‎Lliçons SQL‎ > ‎T07 Conjunts‎ > ‎

Conjunts 2

L'operador EXISTS

EXISTS és un operador d’un únic argument i el resultat del qual és un valor de veritat, informa de la presència o no de tuples en una taula, 

     [NOT] EXISTS (orden select) 

L'operador exists ens informa de si una subconsulta ha obtingut algun resultat: torna verdader si hi ha al menys una tupla en la relació derivada i fals si la relació derivada és buida.


Tenim professors?

select exists (select 1 from profesores) resposta

respuesta
1

Ja que no necessitem res de la taula profesores excepte saber si hi ha o no files, per això ens basta amb "select 1": el resultat de la subconsulta seria tantes files amb "1" com a files hi haja en la taula profesores.

 

És més habitual compondre una condició que ens permeta filtrar files.


Totes les dades de les assignatures que són impartides per algun professor

select * from asignaturas a where exists (select 1 from imparte i where i.asignatura=a.codigo)
codigodescripcioncreditoscreditosp
DGBDDISEÑO Y GESTION DE BASES DE DATOS6.03.0
FBDFUNDAMENTOS DE LAS BASES DE DATOS6.01.5
PCPROGRAMACION CONCURRENTE6.01.5


Evidentement, el resultat seria el mateix si demanàrem

select * from asignaturas where codigo IN (select asignatura from imparte)

Eficiència

Encara que per a la quantitat de files que estem manejant tant en la BD Exemple com en TiendaOnLine la diferència és irrellevant, l’ús de exists pot ser adequat per a millorar el rendiment de certes consultes. Siga, per exemple:

select distinct nombre
from profesores p, imparte i
where p.dni = i.dni;

En realitat, només ens interessa saber el nom dels professors que imparteixen alguna assignatura, sense reparar en quines són aqueixes assignatures. Per a això, és millor utilitzar:

select nombre from profesores p
where exists (select * from imparte where dni = p.dni);

nombre
EVA GOMEZ
RAFAEL ROMERO

L’avantatge d’aquesta segona consulta és que la subselect només necessita tornar una fila per cada professor, mentres que la primera primer obté la concatenació de tots els codis d’assignatura amb el seu professor corresponent i després projecta sobre la columna nom.

Podem suposar un estat de la base de dades que continguera 1000 assignatures, i que els 3 professors imparteixen totes les assignatures: la primera consulta treballaria amb 3000 files, mentres que la segona no necessitaria més de 3.

No obstant això, el que s’ha dit ací ha de considerar-se com un apunt sobre la necessitat en certs entorns de millorar el rendiment d’aquelles consultes que són crítiques per la seua freqüència o per un gran volum de dades, açò és, "ajudar" a l’optimitzador de consultes.

select nombre from profesores p
where exists
(select codigo from asignaturas a
where exists
(select asignatura from prepara pp
where pp.asignatura=a.codigo and p.dni=pp.dni)
)

seria equivalente a "nom dels professors que preparen alguna assignatura", que se soluciona molt més fácil amb

select nombre
from profesores p, prepara pp
where p.dni=pp.dni

nombre
EVA GOMEZ
RAFAEL ROMERO


O si neguem només uno dels exists

Nom dels professors que no preparen totes les assignatures (que hi ha al menys una assignatura que no prepara )

select nombre from profesores p
where exists
(select codigo from asignaturas a
where not exists
(select asignatura from prepara pp
where pp.asignatura=a.codigo and p.dni=pp.dni)
)

nombre
EVA GOMEZ
MANUEL PALOMAR


Alternatives

A vegades, el resultat buscat permet un altre tipus de solucions. 

Nom dels professors que preparen totes les assignatures.

select nombre from profesores p, prepara pp
where p.dni = pp.dni
group by p.dni, nombre
having count(*) = (select count(*) from asignaturas);



Nom dels professors que no preparen cap assignatura.

select nombre from profesores p
where not exists
(select codigo from asignaturas a
where exists
(select asignatura from prepara pp
where pp.asignatura=a.codigo and p.dni=pp.dni)
);
 
select nombre from profesores p
where not exists
(select asignatura from prepara pp
where p.dni=pp.dni);
  

Realment, l’alternativa més clara i fàcil d’entendre és aquesta: 

select nombre from profesores
where dni NOT IN (select dni from prepara);


nombre
MANUEL PALOMAR



Divisió

NOTA: l'operador divisió no s'exigirà en cap prova des de el curs 2013-14. El text que segueix es manté com a material addicional per als que tinguen interès a ampliar coneixements d’àlgebra relacional i SQL.

La divisió com a operador definit en l’àlgebra relacional ens dóna com resultat "quelcom que es relaciona amb tots els que compleixen una condició". Per exemple,

IMPARTE[asignatura, dni] ÷ (PROFESORES[dni])

Aquesta expressió donaria com resultat els codis de les assignatures que són impartides per tots els professors de la meua base de dades. No existeix, la divisió, com a tal operador en SQL pel que hem de simular-lo amb exists.

Exists és un operador d’un únic argument i el resultat del qual és un valor de veritat, informa de la presència o no de tuples en una taula. 

     [NOT] EXISTS (orden select) 

L’operador exists ens informa de si una subconsulta ha obtingut algun resultat: torna verdader si hi ha al menys una tupla en la relació derivada i fals si la relació derivada és buida.


La divisió de l'àlgebra relacional amb EXISTS

Un dels possibles usos, encara que certament confús al principi, és el de resoldre consultes del tipus "x fa quelcom amb tots els i de la nostra base de dades". 

Suposem una taula addicional en la nostra BD Exemple, PREPARA, que és una relació molts a molts entre assignatures i professors, i que podria representar quins professors intervenen en la preparació de cada assignatura. El seu contingut és el que es mostra a continuació:


Professors que preparen totes les assignatures.

select nombre from profesores p
where not exists
(select codigo from asignaturas a
where not exists
(select asignatura from prepara pp
where pp.asignatura=a.codigo and p.dni=pp.dni)
)

nombre
RAFAEL ROMERO



Reformularem la consulta seguint les aparicions de l’operador exists dins de les successives ordes select:

"Nom dels professors tal que no hi ha cap assignatura que no prepare ell".

Suposem que la forma que té el SGBD de resoldre aquesta sentència és la següent :
  1. En la primera select recorrem la taula de professors.
  2. Per a cada professor de l'anterior, la segona select recorre la taula d'assignatures.
  3. Per a cada professor i assignatura de les anteriors comprova que el primer prepara la segona.
Comments