Subconsultes
Llic猫ncia: CC BY-NC-SA 4.0
(Reconeixement - NoComercial - CompartirIgual) 馃叚
Introducci贸
Fins aquest moment, hem realitzat consultes que relacionen m煤ltiples taules per extraure informaci贸
de la base de dades.
No obstant aix貌, de vegades ens podem trobar en la situaci贸 que necessecitem un valor desconegut per realitzar
una consulta, per貌 podriem recuperar aquest valor utilitzant una altra consulta.
Exemple
- Obt茅 els productes que s贸n m茅s cars que la mitjana.
select p.id, p.nom, p.preu
from producte p
where p.preu >= preu_mitja;
Aquesta consulta necessita del valor preu_mitja per recuperar els productes requeris.
Podr铆em calcular aquest valor amb una altra consulta:
select avg(p.preu) preu_mitja
from producte p;
Soluci贸
Per escriure una 煤nica consulta que ens retorne el resultat dessitjat, podem combinar-les
utilitzant una subconsulta:
select p.id, p.nom, p.preu
from producte p
where p.preu >= (
select avg(preu) preu_mitja
from producte;
);
Definici贸
Una subconsulta 茅s una consulta que es realitza dins d'una altra consulta, la qual s'executa abans que la consulta principal.
El resultat que s'obt茅 en la subconsulta 茅s utilitzat en l'execuci贸 de la consulta principal.
Les subconsultes s贸n sent猫ncies SELECT que apareixen dins d'altres sent猫ncies. Tenen la mateixa sintaxi que una consulta normal, per貌
en aquest cas, sempre van entre par猫ntesi (). Aquestes consultes poden ser utilitzades en les clausules WHERE, FROM i HAVING.
Documentaci贸
Documentaci贸 oficial sobre subconsultes:
Subconsultes monoregistre
Aquest tipus de subconsultes s贸n aquelles que sols retornen un 煤nic valor d'un 煤nic registre i poden ser utilitzades com un escalar,
mantenint les caracter铆sitiques i operacions que es poden realitzar amb el tipus de dades del valor que s'ha retornat
Documentaci贸
Documentaci贸 oficial sobre subconsultes monoregistre:
Si s'utilitza en la cl脿usula WHERE es poden utilitzar els comparadors que s'utilitzen normalment:
=, !=, <>, <, <=, >, >=
BETWEEN, NOT BETWEEN, LIKE
Exemple
- Obt茅 els productes que s贸n m茅s cars que la mitjana.
select p.id, p.nom, p.preu
from producte p
where p.preu >= (
select avg(preu) preu_mitja
from producte;
);
Subconsultes multiregistre
Aquest tipus de subconsultes s贸n aquelles que retornen un valor de m茅s d'un registre (llista de valors).
Documentaci贸
Documentaci贸 oficial sobre subconsultes multiregistre:
En aquest cas, no podem utilitzar els operadors monoregistre i hem d'utilitzar operadors
que ens permeten treballar amb una llista de valors:
IN, NOT IN, EXISTS, NOT EXISTS, ANY, SOME, ALL
[NOT] IN
Els operadors multiregistre IN i NOT IN s'utilitzen per saber si un valor est脿 entre un conjunt de valors o no.
Documentaci贸
Documentaci贸 oficial sobre subconsultes amb [NOT] IN:
Exemple
- Obt茅 el DNI dels professors que no s贸n tutors de cap grup.
select pr.dni, pe.nom
from professor pr
inner join persona pe
on pe.dni = pr.dni
where pr.dni not in (
select distinct dni_tutor
from grup
);
[NOT] EXISTS
Els operadors multiregistre EXISTS i NOT EXISTS s'utilitzen per saber si una subconsulta retorna algun valor o no.
Documentaci贸
Documentaci贸 oficial sobre subconsultes amb [NOT] EXISTS:
Exemple
- Obt茅 el DNI dels estudiants que estan cursant l'asignatura amb sigles "BD".
select e.dni
from estudiant e
where exists (
select c.id_modul
from cursa c
inner join modul_professional mp
on c.id_modul = mp.id_modul
where c.nia = e.nia and mp.sigles = "BD"
);
ANY
L'operador multiregistre ANY s'utilitza per comprovar que una condici贸 es compleix per
a qualsevol registre retornat per la subconsulta:
Aquest operador retorna:
TRUE si l'expressi贸 s'avalua a TRUE per a almenys un registre retornat per la subconsulta.-
NULL si l'expressi贸 s'avalua a NULL per a almenys un registre retornat per la subconsulta
i la condici贸 no s'ha avaluat a TRUE per a cap registre.
-
FALSE si l'expressi贸 s'avalua a TRUE per a tots els registres retornats per la subconsulta o si no s'ha retornat cap registre.
Documentaci贸
Documentaci贸 oficial sobre subconsultes amb ANY:
Exemple
- Obt茅 el DNI dels estudiants que han aprovat almenys una assignatra en el curs "23/24".
select e.dni, p.nom, p.cognoms
from estudiant e
inner join persona p
on e.dni = p.dni
where 5 <= any (
select c.nota
from cursa c
where c.nia = e.nia and c.curs_academic = "23/24"
);
ALL
L'operador multiregistre ALL s'utilitza per comprovar que una condici贸 es compleix per
a tots els registres retornats per la subconsulta:
Documentaci贸
Documentaci贸 oficial sobre subconsultes amb ALL:
Aquest operador retorna:
FALSE si l'expressi贸 s'avalua a FALSE per a almenys un valor retornat per la la subconsulta.-
NULL si l'expressi贸 s'avalua a NULL per a almenys un registre retornat per la subconsulta
i la condici贸 no s'ha avaluat a FALSE per a cap registre.
-
TRUE si l'expressi贸 s'avalua a TRUE per a tots els registres retornats per la subconsulta o si no s'ha retornat cap registre..
Exemple
- Obt茅 els estudiants que han aprovat totes les assignatures en les que s'han matriculat.
SELECT e.dni, p.nom, p.cognoms
FROM estudiant e
INNER JOIN persona p
ON e.dni = p.dni
WHERE 5 >= ALL (
SELECT c.nota
FROM cursa c
WHERE c.nia = e.nia
);
L'operador ALL pot no estar definit en tots els SGBD, per貌 es pot utilitzar una expressi贸 matem脿tica
per reescriure la consulta i obtindre el mateix resultat utilitzant la cl脿usula NOT EXISTS.
\[ \forall x | f(x) \equiv \nexists x | \neg f(x) \]
Comprovar que tots els elements de X que compleixen una condici贸 茅s equivalent
a comprovar que no existeix cap element que no compleix la condici贸.
Exemple
La seg眉ent consulta 茅s equivalent a la consulta de l'anterior exemple.
- Obt茅 els estudiants que no han susp猫s cap de les assignatures en les que estan matriculats.
SELECT e.dni, p.nom, p.cognoms
FROM estudiant e
INNER JOIN persona p
ON e.dni = p.dni
WHERE NOT EXISTS (
SELECT c.nota
FROM cursa c
WHERE c.nia = e.nia
and e.nota < 5
);
Rendiment
Bibliografia
馃搶 Aquest document pot quedar desactualitzat despr茅s d鈥檌mprimir-lo. Pots consultar la versi贸 m茅s recent
a la p脿gina web.
馃尶 Abans d鈥檌mprimir aquest document, considera si 茅s realment necessari. Redueix el consum de paper i ajuda a protegir el nostre entorn.
