SUBCONSULTAS SQLPOSTGRES

Una subconsulta és una consulta dins d'una altra consulta. Aquesta subconsulta pot tenir tots els elements que hem vist fins ara.

El lloc on posar una subconsulta dins de la consulta principal pot ser en la clàusual WHERE o en la clàusula HAVING (formant part d'una condició) o en el FROM, i ha d'anar entre parèntesis. Fins i tot es pot posar en el mateix SELECT, és a dir, en les columnes que van després del SELECT.

• Si va en el FROM, la subconsulta serà l'origen de les dades, i per tant s'executarà abans i proporcionarà les dades per a la consulta principal.
• Si va en el WHERE o el HAVING formarà part d'una condició, i així podrem comparar en la consulta principal un camp amb el que torne la subconsulta, per exemple. A banda de les comparacions normals que ja hem vist en el WHERE o el HAVING, podrem posar alguns operadors i predicats especials, com veurem més avant.
• Si va en el mateix SELECT normalment serà per a traure un resultat global que no afecta a la resta de la consulta

\
Sintaxi en el FROM

SELECT … \n FROM ( Subconsulta ) AS Nom_Subconsulta

Anem a posar un exemple per entendre-ho. Volem traure la mitjana de pobles per comarca. Ho podem fer de la següent manera: primer comptem quants pobles hi ha en cada comarca, i una vegada calculat això traem la mitjana.

SELECT AVG(quants) \n FROM (SELECT COUNT(*) AS quants \n FROM POBLACIONS \n GROUP BY nom_c) AS S ;

Observeu que és necessari posar-li un àlias a la columna de la subconsulta (quants) per a poder fer referència a ella en la consulta principal. I per una altra banda, en PostgreSQL les subconsultes que van al FROM han de tenir obligatòriament un àlies. Si no posàrem … AS S (o qualsevol altre nom) ens donaria error.

Com veieu, ja té un nivell de complexitat més que acceptable. Sempre s'executa primer la subconsulta i amb les dades que proporciona, s'executa la consulta principal. Pel grau de complexitat és molt recomanable anar de dins cap a fora, és a dir, pensar bé la subconsulta, fins i tot executar-la per veure si trau el que necessitem (en l'exemple veure si trau el nombre de poblacions de cadascuna de les 34 comarques), i quan estiguem segurs que funciona bé crear la consulta principal.

Sintaxi en el WHERE o el HAVING

SELECT … \n FROM Taula \n WHERE camp operador ( Subconsulta )

Podem observar que el que farem serà comparar algun camp de la taula (o una expressió amb alguna funció) amb el resultat que ve de la subconsulta.

Abans de veure què podem posar com a operador o com a camp o fins i tot veure uns predicats que podrem utilitzar, posarem un exemple, per clarificar les coses. Intentarem traure les comarques amb una altura superior a la mitjana. Calcular la mitjana de les altures és fàcil, i serà la subconsulta. El que farem serà comparar l'altura de cada població amb aquesta mitjana.

**SELECT *** \n FROM POBLACIONS \n WHERE altura > (SELECT AVG(altura) \n

FROM POBLACIONS)

Si executeu la consulta, veureu que l'altura de tots els pobles és superior a 300,44 que és l'altura mitjana (aproximadament, perquè ho calcula amb molta precisió)

No hi ha cap problema en posar dues vegades la mateixa taula. Els camps es refereixen a la taula més propera. I funciona perfectament perquè la subconsulta ens torna un únic valor, la mitjana d'altures, i en la consulta principal es compara cada altura amb aquest valor. Posteriorment veurem com solucionar el problema de que la subconsulta torne més d'un valor.

Els operadors de la condició poden ser de 3 tipus:

• De comparació. És com l'exemple de dalt, però amb qualsevol operador de comparació. Es compara el camp (o l'expressió) amb el resultat de la subconsulta. Si la subconsulta només torna un valor, no hi ha més problema, però si torna més d'un valor (més d'una fila) de moment seria incorrecte (no es pot comparar un camp amb uns quants valors). Posem un altre exemple per il·lustrar. Traure la població més alta es podria fer d'aquesta manera.

**SELECT *** \n FROM POBLACIONS \n WHERE altura = (SELECT MAX(altura) \n

FROM POBLACIONS)

No hi ha problema perquè la subconsulta torna un valor. Però anem a complicar-la anem a veure les poblacions més altes de cada comarca. Podríem intentar-lo d'aquesta manera:

**SELECT *** \n FROM POBLACIONS \n WHERE altura = (SELECT MAX(altura) \n

FROM POBLACIONS

GROUP BY nom_c)

Però ens donaria el següent error:

I és que la subconsulta torna 34 valors (un per cada comarca), i d'aquesta manera no es pot comparar el valor de l'esquerra del igual amb els 34 valors de la dreta. Per a solucionar el problema de quan torna més d'un valor podem utilitzar els predicats ALL , ANY , SOME.

• Si utilitzem ALL el resultat serà cert si la comparació és certa amb TOTS els valors que torna la subconsulta.
• Si utilitzem ANY o SOME (que són sinònims) el resultat serà cert si la comparació és certa amb ALGUN valor de la subconsulta.

En el nostre exemple, segurament ens convindria ANY

**SELECT *** \n FROM POBLACIONS \n WHERE altura = ANY (SELECT MAX(altura) \n

FROM POBLACIONS

GROUP BY nom_c)

Aquesta consulta no funcionarà bé del tot, ja que seleccionarà totes les poblacions que coincideixen amb alguna de les altures màximes, siguen de la seua comarca o no.

\

• L'operador IN. No serà problema que la subconsulta torne un valor o molts. La condició serà certa si el valor del camp (o de l'expressió) està entre la llista de valors que torna la subconsulta. També poden utilitzar NOT IN, i la condició serà certa quan el valor del camp no està entre la llista.

**SELECT *** \n FROM POBLACIONS \n WHERE codm NOT IN (SELECT codm \n FROM INSTITUTS)

• L'operador EXISTS. És segurament el més incòmode. No es compara un camp (o expressió) amb la subconsulta, sinó únicament es posa [NOT] EXISTS (subconsulta) . La condició serà certa si la subconsulta torna alguna fila, i no serà certa si no torna cap fila. Intentem fer el mateix exemple d'abans, el dels pobles sense institut. Hem d'aconseguir que la subconsulta no tinga cap fila en el cas dels que no tenen institut. De paraula ho podem dir així: volem els pobles per als quals no existeix cap fila en INSTITUTS amb el mateix codi de municipi. Ara ja es pot intuir per on van els tirs:

**SELECT *** \n FROM POBLACIONS \n **WHERE NOT EXISTS (SELECT *** \n FROM INSTITUTS \n WHERE codm= POBLACIONS.codm)

mireu com si en la subconsulta posem un camp (en l'exemple cod_m), si el camp és de la taula (o taules) de la subconsulta, es referirà a ell, per això si volem fer referència a un camp de la taula o taules de la consulta principal hem de posar el nom de la taula davant.

Sintaxi en el SELECT

SELECT … ( Subconsulta ) \n FROM Taula

Anem a posar també un exemple per entendre-ho. Anem a calcular la diferència de l'altura de cada població amb la mitjana. La mitjana és un resultat global indepentent de la resta de la consulta, que en aquest cas és molt senzilla perquè hem d'agafar informació simple de les poblacions. La subconsulta també és molt senzilla, perquè només hem de calcular la mitjana d'altures).

SELECT nom, altura, altura - (SELECT AVG(altura) FROM POBLACIONS) \n FROM POBLACIONS

\