<div class="page">
<div class="cover text-center">
<img class="mx-auto" src=/itb/images/logo_mislata.png alt="logo">
# Subconsultes
<div class="text-end fit-content ms-auto my-3 mt-auto pt-3">
<p><strong>Autor:</strong> Joan Puigcerver Ibáñez</p>
<p><strong>Correu electrònic:</strong> j.puigcerveribanez@edu.gva.es</p>
<p><strong>Curs:</strong> 2023/2024</p>
</div>
<div>
<p class="fw-bold mb-0">Llicència: BY-NC-SA</p>
<p class="d-none d-md-block">(Reconeixement - No Comercial - Compartir Igual)</p>
<a href="https://creativecommons.org/licenses/by-nc-sa/4.0/deed.ca" target="_blank">
<img class="mx-auto" src="/itb/images/license.png" alt="Licence"/>
</a>
</div><!--license-->
</div><!--cover-->
</div><!--page-->
{:toc}
## 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.
:::: example
- _Obté els productes que són més cars que la mitjana._
```sql
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:
```sql
select avg(p.preu) preu_mitja
from producte p;
```
::: solution
Per escriure una única consulta que ens retorne el resultat dessitjat, podem combinar-les
utilitzant una __subconsulta__:
```sql
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`.
::: docs
Documentació oficial sobre subconsultes:
- MariaDB: https://mariadb.com/kb/en/subqueries/
- MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/subqueries.html
:::
## 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
::: docs
Documentació oficial sobre subconsultes monoregistre:
- MariaDB: https://mariadb.com/kb/en/subqueries-scalar-subqueries/
- MySQL 8.0:
- https://dev.mysql.com/doc/refman/8.0/en/subqueries.html
- https://dev.mysql.com/doc/refman/8.0/en/comparisons-using-subqueries.html
:::
Si s'utilitza en la clàusula `WHERE` es poden utilitzar els comparadors que s'utilitzen normalment:
```
=, !=, <>, <, <=, >, >=
BETWEEN, NOT BETWEEN, LIKE
```
::: example
- _Obté els productes que són més cars que la mitjana._
```sql
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).
::: docs
Documentació oficial sobre subconsultes multiregistre:
- MariaDB: https://mariadb.com/kb/en/subqueries-scalar-subqueries/
- MySQL 8.0:
- https://dev.mysql.com/doc/refman/8.0/en/any-in-some-subqueries.html
:::
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.
::: docs
Documentació oficial sobre subconsultes amb `[NOT] IN`:
- MariaDB: https://mariadb.com/kb/en/in/
- MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/any-in-some-subqueries.html
:::
::: example
- _Obté el DNI dels professors que no són tutors de cap grup._
```sql
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.
::: docs
Documentació oficial sobre subconsultes amb `[NOT] EXISTS`:
- MariaDB: https://mariadb.com/kb/en/subqueries-and-exists/
- MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/exists-and-not-exists-subqueries.html
:::
::: example
- _Obté el DNI dels estudiants que estan cursant l'asignatura amb sigles "BD"._
```sql
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.
::: docs
Documentació oficial sobre subconsultes amb `ANY`:
- MariaDB: https://mariadb.com/kb/en/subqueries-and-any/
- MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/any-in-some-subqueries.html
:::
::: example
- _Obté el DNI dels estudiants que han aprovat almenys una assignatra en el curs "23/24"._
```sql
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:
::: docs
Documentació oficial sobre subconsultes amb `ALL`:
- MariaDB: https://mariadb.com/kb/en/subqueries-and-all/
- MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/all-subqueries.html
:::
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..
::: example
- _Obté els estudiants que han aprovat totes les assignatures en les que s'han matriculat._
```sql
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ó._
::: example
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._
```sql
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
- https://stackoverflow.com/questions/2577174/join-vs-sub-query
## Bibliografia
- https://stackoverflow.com/questions/2577174/join-vs-sub-query