El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència

HOME

Les tres lliçons anteriors han respost el què: què retorna una subconsulta, si està correlacionada, si pregunta per existència. Aquesta respon el on. Perquè la mateixa subconsulta col·locada al SELECT, al FROM o al WHERE no fa el mateix, no costa el mateix i no admet el mateix.

I hi ha una clàusula que encara no has fet servir i que és, amb diferència, la més potent: el FROM. Una subconsulta allà s'anomena taula derivada i permet una cosa que cap altra construcció del curs no permetia: agregar un resultat ja agregat. Amb ella calcularàs per fi el tiquet mitjà de 36,40 € des del seu origen, creuaràs dos agregats de granularitat diferent perquè els 727,95 € de producte i els 118,25 € de ports quadrin sense inflar-se —el problema que arrossegues des del mòdul 3— i coneixeràs LATERAL, l'excepció que permet correlacionar una taula derivada.

Contingut

Taula resum: què canvia segons on la posis
Al SELECT: la columna calculada
Al FROM: la taula derivada
LATERAL: la taula derivada que sí que es pot correlacionar
Al WHERE i al HAVING
A INSERT, UPDATE i DELETE
Taula de decisió: donat un problema, on posar-la
Errors habituals i consells
Exercicis
Conclusió

Taula resum: què canvia segons on la posis

Clàusula	Què ha de retornar	Correlacionada?	Cost típic	Alternativa preferible
`SELECT`	Escalar: 1 fila, 1 columna	Sí, i gairebé sempre ho és	1 execució per fila	`LEFT JOIN` + `GROUP BY`
`FROM`	Taula: qualsevol forma	No, llevat que hi hagi `LATERAL`	1 (o 1 per fila amb `LATERAL`)	CTE (10-02) amb 3+ nivells
`WHERE`	Escalar, o llista per a `IN`/`EXISTS`	Sí	1, o 1 per fila si correlaciona	`JOIN` si en necessites les columnes
`HAVING`	Escalar	Sí (per grup)	1	—
`SET` d'un `UPDATE`	Escalar	Sí	1 per fila actualitzada	`UPDATE ... FROM` (05-03)

Tres regles es dedueixen de la taula: al SELECT només hi cap un valor (dues files o dues columnes i la consulta rebenta); al FROM l'àlies és obligatori, sempre, encara que no el facis servir; i una taula derivada no veu les altres taules del seu mateix FROM, que és la restricció que LATERAL aixeca.

Al `SELECT`: la columna calculada

Una subconsulta escalar al SELECT es comporta com una columna més. Ja la vas fer servir a 07-02: aquí interessa el seu límit i la seva alternativa.

SELECT c.id,
       c.nom || ' ' || c.cognoms AS client,
       c.pais,
       (SELECT COUNT(*) FROM comandes AS co WHERE co.client_id = c.id) AS comandes,
       (SELECT COALESCE(ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)), 2), 0)
        FROM comandes AS co
        JOIN linies_comanda AS lc ON lc.comanda_id = co.id
        WHERE co.client_id = c.id) AS total_comprat
FROM clients AS c
ORDER BY total_comprat DESC, c.id
LIMIT 4;

id	client	pais	comandes	total_comprat
7	Sofia Moreira Costa	Portugal	2	111.88
1	Lucía Martínez Soler	Espanya	3	107.60
9	Camille Dubois	França	2	70.87
10	Julien Moreau	França	1	66.90

(4 primeres de 15 files.) Els tres clients sense comandes tanquen la llista amb 0 i 0.00, gràcies al COALESCE de 06-04.

Les tres propietats que defineixen aquest ús: només pot retornar un valor (si necessites el total i el nombre de comandes, calen dues subconsultes, i cadascuna recorre comandes pel seu compte); no filtra files, els 15 clients continuen allà, comportant-se com un LEFT JOIN sense ser-ho; i s'executa una vegada per fila: dues subconsultes × 15 clients = 30 execucions.

La mateixa consulta amb `LEFT JOIN` i `GROUP BY`

SELECT c.id,
       c.nom || ' ' || c.cognoms AS client,
       c.pais,
       COUNT(DISTINCT co.id) AS comandes,
       COALESCE(ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)), 2), 0) AS total_comprat
FROM clients AS c
LEFT JOIN comandes       AS co ON co.client_id = c.id
LEFT JOIN linies_comanda AS lc ON lc.comanda_id = co.id
GROUP BY c.id, c.nom, c.cognoms, c.pais
ORDER BY total_comprat DESC, c.id
LIMIT 4;

Resultat idèntic, les mateixes 15 files amb els mateixos valors. Però la feina és molt diferent:

	Subconsultes al `SELECT`	`LEFT JOIN` + `GROUP BY`
Passades per `comandes`	30 (2 × 15 clients)	1
Llegibilitat amb 2 columnes / amb 5	Molt bona / dolenta	Bona / bona
Risc de `COUNT(*)` inflat	Cap	Alt: cal fer servir `COUNT(DISTINCT co.id)`
Afegir una mètrica nova	Copiar i enganxar un bloc	Afegir un agregat

Fixa't en la penúltima fila, que és el matís important: la versió amb JOIN necessita COUNT(DISTINCT co.id) perquè el segon LEFT JOIN multiplica les files de cada comanda per les seves línies. És exactament el problema de 04-04, i la versió amb subconsultes n'és immune. Cap de les dues formes no és millor sempre: amb una o dues columnes la subconsulta es llegeix millor; a partir de tres, el GROUP BY guanya de llarg. La discussió completa és 07-05.

Al `FROM`: la taula derivada

Una subconsulta al FROM produeix una taula derivada: un resultat intermedi que la consulta externa tracta com una taula real. És la clàusula que desbloqueja el patró més útil de l'anàlisi: agregar dues vegades.

L'àlies obligatori

-- ⚠️ INCORRECTA
SELECT AVG(total) FROM (SELECT SUM(quantitat) AS total FROM linies_comanda GROUP BY comanda_id);

ERROR:  subquery in FROM must have an alias
HINT:  For example, FROM (SELECT ...) [AS] foo.

Tota taula derivada necessita nom, encara que no el facis servir: les seves columnes han de poder qualificar-se (t.total), i sense nom de taula això és impossible.

Nota de dialecte: PostgreSQL, MySQL, MariaDB i SQL Server exigeixen l'àlies; SQLite i Oracle permeten ometre'l. Escriu-lo sempre: és portable i fa la consulta llegible.

Cas 1: agregar dues vegades — el tiquet mitjà, des del seu origen

A 07-01 vas calcular el tiquet mitjà com a SUM(import) / COUNT(DISTINCT comanda_id). És correcte, però és una drecera: la formulació honesta és calcular el total de cada comanda i després fer la mitjana d'aquells totals. Són dues agregacions encadenades, i només una taula derivada les permet.

SELECT COUNT(*)               AS comandes,
       ROUND(AVG(t.total), 2) AS tiquet_mitja,
       ROUND(MIN(t.total), 2) AS tiquet_minim,
       ROUND(MAX(t.total), 2) AS tiquet_maxim,
       ROUND(SUM(t.total), 2) AS facturacio
FROM (SELECT co.id AS comanda_id,
             ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)), 2) AS total
      FROM comandes        AS co
      JOIN linies_comanda  AS lc ON lc.comanda_id = co.id
      GROUP BY co.id) AS t;

comandes	tiquet_mitja	tiquet_minim	tiquet_maxim	facturacio
20	36.40	22.60	66.90	727.95

Aquí tens les xifres canòniques del curs, i ara per construcció: 20 tiquets, mitjana de 36,40 €, facturació de 727,95 €; el tiquet més barat és la comanda 20 de la Camille i el més car la 12 d'en Julien.

El que fa possible el resultat és que la taula derivada canvia la granularitat: hi entren 47 línies i en surten 20 comandes, i la consulta externa agrega sobre aquelles 20. Sense ella, AVG sobre les línies donaria els 15,49 € d'import mitjà de línia, que és una altra cosa.

Cas 2: agregar i després filtrar

Un cop tens la taula derivada, la filtres com qualsevol taula:

SELECT t.comanda_id, c.nom || ' ' || c.cognoms AS client, t.total
FROM (SELECT co.id AS comanda_id, co.client_id,
             ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)), 2) AS total
      FROM comandes       AS co
      JOIN linies_comanda AS lc ON lc.comanda_id = co.id
      GROUP BY co.id, co.client_id) AS t
JOIN clients AS c ON c.id = t.client_id
WHERE t.total > 36.40
ORDER BY t.total DESC;

comanda_id	client	total
12	Julien Moreau	66.90
8	Sofia Moreira Costa	64.88
10	Camille Dubois	48.27
17	Sofia Moreira Costa	47.00

(4 primeres de 6 files; tanquen les comandes 9 d'en Tiago, 44,60 €, i 1 de la Lucía, 42,10 €.) 6 comandes de 20 superen el tiquet mitjà. El filtre és al WHERE de la consulta externa, no en un HAVING: per a ella, t.total és una columna normal. Una taula derivada converteix agregats en columnes corrents, i això simplifica moltíssim l'escriptura.

Cas 3: dos agregats de granularitat diferent — el problema dels ports

Aquest és el cas que el curs porta pendent des del mòdul 3. Les despeses d'enviament viuen a comandes (una per comanda) i els imports a linies_comanda (diverses per comanda): sumar-los a la mateixa consulta amb un JOIN donava 278,70 € de ports en lloc de 118,25 €, perquè cada comanda es comptava tantes vegades com línies tenia. La solució és agregar cada cosa pel seu costat i unir els resultats ja agregats:

SELECT c.id,
       c.nom || ' ' || c.cognoms AS client,
       com.comandes,
       lin.productes,
       com.ports,
       ROUND(lin.productes + com.ports, 2) AS total
FROM clients AS c
JOIN (SELECT client_id, COUNT(*) AS comandes, SUM(despeses_enviament) AS ports
      FROM comandes GROUP BY client_id) AS com ON com.client_id = c.id
JOIN (SELECT co.client_id,
             ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)), 2) AS productes
      FROM comandes       AS co
      JOIN linies_comanda AS lc ON lc.comanda_id = co.id
      GROUP BY co.client_id) AS lin ON lin.client_id = c.id
ORDER BY total DESC;

id	client	comandes	productes	ports	total
7	Sofia Moreira Costa	2	111.88	19.80	131.68
1	Lucía Martínez Soler	3	107.60	4.95	112.55
9	Camille Dubois	2	70.87	25.00	95.87

(3 primeres de 12 files.) I les columnes quadren: sumades les 12 files donen 727,95 € de producte + 118,25 € de ports = 846,20 €, les tres xifres canòniques del curs. Ni un cèntim inflat.

La clau és que cada derivada ja ve a granularitat de client —com té 12 files i lin també—, així que en unir-les cada comanda hi aporta els seus ports una sola vegada: el SUM s'ha fet abans del JOIN. La versió global cap en tres línies:

SELECT lin.facturacio, com.ports, ROUND(lin.facturacio + com.ports, 2) AS total
FROM (SELECT ROUND(SUM(quantitat * preu_unitari * (1 - descompte)), 2) AS facturacio
      FROM linies_comanda) AS lin
CROSS JOIN (SELECT SUM(despeses_enviament) AS ports FROM comandes) AS com;

facturacio	ports	total
727.95	118.25	846.20

A partir de tres nivells, fes servir CTE. Dues taules derivades encara es llegeixen; amb tres, o amb una derivada dins d'una altra dins d'una altra, el sagnat es menja la pantalla. La solució és donar nom a cada pas amb WITH, i això és 10-02: tot el d'aquesta secció es reescriu allà amb la meitat de parèntesis.

`LATERAL`: la taula derivada que sí que es pot correlacionar

Ja saps que una taula derivada no veu les altres taules del seu mateix FROM:

-- ⚠️ INCORRECTA
SELECT cat.nom, top.nom FROM categories AS cat,
     (SELECT p.nom FROM productes AS p WHERE p.categoria_id = cat.id LIMIT 2) AS top;

ERROR:  invalid reference to FROM-clause entry for table "cat"
HINT:  There is an entry for table "cat", but it cannot be referenced from this part of the query.

La paraula clau LATERAL aixeca aquesta restricció: diu al motor "avalua aquesta subconsulta una vegada per cada fila del que hi ha a la seva esquerra". És un for sobre la taula anterior, i es combina amb CROSS JOIN LATERAL o amb LEFT JOIN LATERAL ... ON TRUE. El seu cas natural és el top-N per grup:

SELECT cat.id, cat.nom AS categoria, top.producte, top.unitats
FROM categories AS cat
CROSS JOIN LATERAL (
        SELECT p.nom AS producte, SUM(lc.quantitat) AS unitats
        FROM productes        AS p
        JOIN linies_comanda   AS lc ON lc.producte_id = p.id
        WHERE p.categoria_id = cat.id
        GROUP BY p.id, p.nom
        ORDER BY unitats DESC, p.id
        LIMIT 2) AS top
ORDER BY cat.id, top.unitats DESC;

id	categoria	producte	unitats
1	Alimentació	Arròs integral ecològic 1 kg	14
1	Alimentació	Tomàquet triturat ecològic 400 g	14
2	Cosmètica natural	Bàlsam labial de calèndula 15 ml	7
3	Llar sostenible	Bosses reutilitzables de cotó (pack 5)	4
4	Begudes	Kombutxa de gingebre 750 ml	12
5	Higiene personal	Raspall de dents de bambú	9

(6 de les 9 files.) Les quatre primeres categories aporten els seus dos productes més venuts; Higiene personal només en té un de venut (el raspall, perquè el desodorant no es va vendre mai) i aporta una fila. I Complements no hi apareix, perquè la seva subconsulta retorna zero files i CROSS JOIN LATERAL es comporta com un INNER JOIN; per veure-la amb NULL es fa servir LEFT JOIN LATERAL (...) AS top ON TRUE, que dona 10 files. Aquell ON TRUE no és cap adorn: la sintaxi exigeix una condició d'unió i la correlació ja és a dins, així que no queda res per posar-hi.

	Taula derivada normal	`LATERAL`
Veu les taules anteriors del `FROM`	No	Sí
Vegades que s'avalua	1	1 per fila de l'esquerra
Permet `LIMIT` per grup	No	Sí: és el seu gran avantatge

Nota de dialecte: LATERAL és estàndard SQL:1999 i funciona a PostgreSQL 9.3+, MySQL 8.0.14+ i Oracle 12c+. A SQL Server l'equivalent es diu CROSS APPLY (i OUTER APPLY per a la versió amb NULL), amb la mateixa semàntica i sense la paraula LATERAL. SQLite no ho admet. I per a aquest mateix problema hi ha una tercera via, sovint millor: ROW_NUMBER() OVER (PARTITION BY categoria_id ORDER BY unitats DESC) filtrant per <= 2, que és 10-03.

Al `WHERE` i al `HAVING`

És el territori de 07-01 i 07-03: al WHERE hi caben subconsultes escalars (> (SELECT AVG(...))), de llista (IN, ANY, ALL) i d'existència (EXISTS), correlacionades o no; i si necessites columnes de la taula interna al resultat, això no és un WHERE, és un JOIN. El HAVING mereix un exemple propi, perquè la seva subconsulta compara l'agregat d'un grup amb un valor calculat sobre un altre conjunt:

SELECT cat.id, cat.nom AS categoria,
       ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)), 2) AS facturacio
FROM linies_comanda AS lc
JOIN productes  AS p   ON lc.producte_id = p.id
JOIN categories AS cat ON p.categoria_id = cat.id
GROUP BY cat.id, cat.nom
HAVING SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte))
       > (SELECT SUM(lc2.quantitat * lc2.preu_unitari * (1 - lc2.descompte))
                 / COUNT(DISTINCT p2.categoria_id)
          FROM linies_comanda AS lc2
          JOIN productes AS p2 ON lc2.producte_id = p2.id)
ORDER BY facturacio DESC;

id	categoria	facturacio
1	Alimentació	256.27
4	Begudes	195.28
2	Cosmètica natural	156.32

3 categories de les 5 amb vendes superen la facturació mitjana per categoria, que és de 145,59 € (727,945 € entre les 5 categories que han venut alguna cosa). Llar sostenible (88,58 €) i Higiene personal (31,50 €) es queden per sota, i Complements ni tan sols arriba al GROUP BY perquè l'INNER JOIN la va descartar.

Fixa't en el divisor: COUNT(DISTINCT p2.categoria_id) compta 5, no 6. Per dividir entre les sis categories del catàleg, el divisor hauria de sortir de categories, no de les vendes. La mitjana depèn de sobre què la calcules.

A `INSERT`, `UPDATE` i `DELETE`

El mòdul 5 et va ensenyar les tres instruccions; ara els pots donar una subconsulta a qualsevol de les seves clàusules.

-- 1. Al WHERE d'un UPDATE: pujar un 10 % les begudes
UPDATE productes SET preu = ROUND(preu * 1.10, 2)
WHERE categoria_id = (SELECT id FROM categories WHERE nom = 'Begudes');
-- 2. CORRELACIONADA al SET: alinear el preu amb la mitjana realment venuda
UPDATE productes AS p
SET preu = (SELECT ROUND(AVG(lc.preu_unitari), 2)
            FROM linies_comanda AS lc WHERE lc.producte_id = p.id)
WHERE EXISTS (SELECT 1 FROM linies_comanda AS lc WHERE lc.producte_id = p.id);
-- 3. En un DELETE: esborrar ressenyes de productes descatalogats
DELETE FROM ressenyes AS r
WHERE EXISTS (SELECT 1 FROM productes AS p WHERE p.id = r.producte_id AND p.actiu = FALSE);

La segona amaga la trampa més perillosa de la lliçó. Sense el WHERE EXISTS final, els tres productes mai venuts rebrien el resultat d'una subconsulta sense files —és a dir NULL— i preu és NOT NULL: la sentència fallaria sencera amb null value in column "preu" violates not-null constraint. Si la columna admetés nuls seria pitjor: es quedaria en NULL sense dir res. La regla de 05-03 s'amplia: en un UPDATE amb subconsulta al SET, el WHERE ha de garantir que aquella subconsulta troba alguna cosa.

Taula de decisió: donat un problema, on posar-la

El que vols	On va la subconsulta	Forma
Filtrar per un llindar calculat o per pertinença a una llista	`WHERE`	`> (SELECT AVG(...))`, `IN (SELECT ...)`
Filtrar per existència o absència	`WHERE`	`EXISTS` / `NOT EXISTS`
Filtrar grups per un valor global	`HAVING`	`> (SELECT ...)`
Afegir una dada calculada per fila (o diverses)	`SELECT` escalar correlacionada; amb 3+, `LEFT JOIN` + `GROUP BY`	—
Agregar sobre un agregat	`FROM`	Taula derivada
Creuar mètriques de granularitat diferent	`FROM` (dues derivades)	`JOIN` entre elles
Un "top N" per cada fila d'una altra taula	`FROM`	`LATERAL`
Reutilitzar el mateix càlcul tres vegades	Cap: CTE	`WITH` (10-02)

Errors habituals i consells

Oblidar l'àlies d'una taula derivada. subquery in FROM must have an alias. Posa'l sempre, encara que no el facis servir. I referenciar una altra taula del mateix FROM des d'una derivada dona invalid reference to FROM-clause entry: per a això hi ha LATERAL.
Posar una subconsulta multifila al SELECT. more than one row returned by a subquery used as an expression: allà només hi cap un valor.
Sumar despeses_enviament després d'unir amb linies_comanda. És l'error de 04-04 —278,70 € en lloc de 118,25 €—: agrega cada cosa pel seu costat en dues derivades.
Confondre el tiquet mitjà (36,40 €, mitjana de 20 comandes) amb l'import mitjà de línia (15,49 €, mitjana de 47). Només la taula derivada dona el primer. I comptar amb COUNT(*) després de diversos LEFT JOIN infla: cada comanda apareix tantes vegades com línies té, així que COUNT(DISTINCT co.id).
Un UPDATE amb subconsulta al SET sense un WHERE que l'acoti. Les files sense coincidència reben NULL: o rebenta la restricció, o corromp les dades en silenci.
Consell: construeix les derivades de dins cap enfora. Escriu la subconsulta sola, executa-la, comprova quantes files i quina granularitat té, i només aleshores embolcalla-la: una derivada sempre es pot executar aïllada (llevat que porti LATERAL).
Consell: anomena-la pel que conté, no t1 i t2: com, lin, vendes_per_client. I compta les files de cada nivell —47 línies → 20 comandes → 1 fila—: si un nivell no redueix el que esperaves, l'error és allà i no al de dalt.

Exercicis

Exercici 1

Direcció vol el tiquet mitjà per país: per a cada país, el nombre de comandes, la facturació de producte i el tiquet mitjà (mitjana dels totals de les seves comandes). Fes servir una taula derivada. Després respon: seria diferent el resultat si calculessis SUM(import) / COUNT(DISTINCT comanda_id) sense taula derivada?

Exercici 2

Un company vol l'informe "per client: comandes, línies, unitats, productes diferents i facturació" i ha començat així:

SELECT c.id, c.nom,
       (SELECT COUNT(*) FROM comandes co WHERE co.client_id = c.id) AS comandes,
       (SELECT COUNT(*) FROM comandes co JOIN linies_comanda lc ON lc.comanda_id = co.id
        WHERE co.client_id = c.id) AS linies,
       (SELECT SUM(lc.quantitat) FROM comandes co JOIN linies_comanda lc ON lc.comanda_id = co.id
        WHERE co.client_id = c.id) AS unitats
FROM clients c;

Quantes execucions de subconsulta suposa tal com està, i quantes si hi afegeix les dues columnes que hi falten?
Reescriu-ho amb un LEFT JOIN i GROUP BY, tenint cura del recompte de comandes.
Quina diferència hi haurà al resultat per als clients 13, 14 i 15?

Exercici 3

Màrqueting vol, per a cada client que hagi comprat, la seva comanda més cara: id de la comanda, data i import. Escriu-ho amb LATERAL i explica per què una taula derivada normal no serviria.

Solucions

Solució 1

SELECT t.pais,
       COUNT(*)               AS comandes,
       ROUND(SUM(t.total), 2) AS facturacio,
       ROUND(AVG(t.total), 2) AS tiquet_mitja
FROM (SELECT co.id, c.pais,
             ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)), 2) AS total
      FROM comandes       AS co
      JOIN clients        AS c  ON c.id = co.client_id
      JOIN linies_comanda AS lc ON lc.comanda_id = co.id
      GROUP BY co.id, c.pais) AS t
GROUP BY t.pais
ORDER BY tiquet_mitja DESC;

pais	comandes	facturacio	tiquet_mitja
Portugal	3	156.48	52.16
França	3	137.77	45.92
Espanya	14	433.70	30.98

3 + 3 + 14 = 20 comandes i 156,48 + 137,77 + 433,70 = 727,95 €: quadra amb les xifres canòniques. I la lectura confirma el que ja vas veure a 07-01: les comandes estrangeres són sensiblement més grans —52,16 € i 45,92 € enfront de 30,98 €—, coherent amb uns ports de 9,90 € i 12,50 € que empenyen a agrupar la compra.

Sobre la pregunta: aquí el resultat seria el mateix, perquè AVG dels totals per comanda i SUM(import) / COUNT(DISTINCT comanda_id) són aritmèticament idèntics quan cada comanda pertany a un sol país. La taula derivada guanya igualment per dos motius: es llegeix molt millor —diu literalment "mitjana dels totals de les comandes"— i permet calcular el que la drecera no pot, com ara MIN(t.total), MAX(t.total) o la mediana.

Solució 2

1. Les execucions. Tres subconsultes × 15 clients = 45; amb les dues columnes que hi falten, 75. I les cinc recorren les mateixes dues taules amb el mateix filtre: cinc vegades la mateixa feina. 2. La reescriptura:

SELECT c.id,
       c.nom,
       COUNT(DISTINCT co.id)          AS comandes,
       COUNT(lc.id)                   AS linies,
       COALESCE(SUM(lc.quantitat), 0) AS unitats,
       COUNT(DISTINCT lc.producte_id) AS productes_diferents,
       COALESCE(ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)), 2), 0) AS facturacio
FROM clients AS c
LEFT JOIN comandes       AS co ON co.client_id = c.id
LEFT JOIN linies_comanda AS lc ON lc.comanda_id = co.id
GROUP BY c.id, c.nom
ORDER BY c.id;

id	nom	comandes	linies	unitats	productes_diferents	facturacio
1	Lucía	3	9	15	8	107.60
7	Sofia	2	5	11	4	111.88
13	Núria	0	0	0	0	0.00

(3 de les 15 files, a tall de mostra.) COUNT(DISTINCT co.id) és obligatori: amb COUNT(co.id) a seques, la Lucía tindria 9 comandes en comptes de 3, una per cada línia. És exactament la trampa de 04-04. En canvi COUNT(lc.id) sí que va sense DISTINCT, perquè cada línia és única.

3. Els clients 13, 14 i 15 apareixen en les dues versions amb els mateixos valors: 0 on hi ha COUNT i 0.00 on el COALESCE cobreix el NULL de SUM. Ni la subconsulta al SELECT ni el LEFT JOIN no els eliminen; el que sí que els hauria eliminat és un INNER JOIN, i per això el LEFT no és negociable aquí.

Solució 3

SELECT c.id,
       c.nom || ' ' || c.cognoms AS client,
       mx.comanda_id, mx.data_comanda, mx.total
FROM clients AS c
CROSS JOIN LATERAL (
        SELECT co.id AS comanda_id, co.data_comanda,
               ROUND(SUM(lc.quantitat * lc.preu_unitari * (1 - lc.descompte)), 2) AS total
        FROM comandes       AS co
        JOIN linies_comanda AS lc ON lc.comanda_id = co.id
        WHERE co.client_id = c.id
        GROUP BY co.id, co.data_comanda
        ORDER BY total DESC, co.id
        LIMIT 1) AS mx
ORDER BY mx.total DESC, c.id;

id	client	comanda_id	data_comanda	total
10	Julien Moreau	12	2025-10-01	66.90
7	Sofia Moreira Costa	8	2025-06-28	64.88
9	Camille Dubois	10	2025-08-03	48.27

(3 primeres de 12 files.) Els clients 13, 14 i 15 no hi apareixen: la seva subconsulta lateral retorna zero files i CROSS JOIN LATERAL els descarta, que és just el que demanava l'enunciat. Amb LEFT JOIN LATERAL ... ON TRUE en sortirien les 15 files, amb NULL a les tres últimes.

Per què una taula derivada normal no serveix: hauríem d'escriure-hi WHERE co.client_id = c.id, i una derivada corrent no veu c (invalid reference to FROM-clause entry for table "c"). Ho podries esquivar agregant per client i unint per MAX(total), però això obliga a un segon JOIN per recuperar l'id i la data de la comanda guanyadora, i duplica files si hi ha empat. LATERAL ho resol en una passada perquè el LIMIT 1 s'aplica per client, i això cap altra construcció del mòdul no ho pot fer. L'alternativa moderna és ROW_NUMBER() (10-03).

Conclusió

El on importa tant com el què:

Al SELECT, una subconsulta escalar és una columna calculada: no filtra files i s'executa una vegada per fila. Amb una o dues columnes es llegeix molt bé; a partir de tres guanya el LEFT JOIN + GROUP BY, tenint cura del COUNT(DISTINCT).
Al FROM, una subconsulta és una taula derivada i necessita àlies obligatòriament (subquery in FROM must have an alias). És l'única manera d'agregar sobre un agregat: 47 línies → 20 comandes → tiquet mitjà de 36,40 €, amb mínim de 22,60 € i màxim de 66,90 €.
Dues derivades de granularitat diferent resolen el problema dels ports que arrossegaves des del mòdul 3: 727,95 € de producte + 118,25 € de ports = 846,20 €, sense inflar res, perquè cada SUM es fa abans del JOIN.
LATERAL és l'excepció que permet correlacionar una taula derivada, i el seu cas natural és el top N per grup: els dos productes més venuts de cada categoria, amb LIMIT aplicat per categoria. A SQL Server es diu CROSS APPLY; a SQLite no existeix; i per a rànquings sol ser millor una funció de finestra (10-03).
A WHERE i HAVING val tot el de 07-01 i 07-03; a UPDATE, una subconsulta al SET exigeix un WHERE que garanteixi que troba alguna cosa, o escriuràs nuls. I a partir de tres nivells la llegibilitat exigeix CTE (10-02).

Ja tens totes les peces: subconsultes escalars, de llista, correlacionades, EXISTS, taules derivades i LATERAL. I amb elles, un problema nou: gairebé totes les preguntes admeten ara dues o tres escriptures diferents, i cap no et diu quina triar. A l'última lliçó del mòdul, subconsultes o JOIN: quin triar, veuràs les quatre grans equivalències enfrontades, la diferència real entre IN i un INNER JOIN —que no és d'estil, sinó de nombre de files—, la taula definitiva de les quatre formes de respondre "què no casa" i el criteri honest per decidir: llegibilitat primer, rendiment mesurat després.

Utilitzar subconsultes en les clàusules SELECT, FROM i WHERE

Contingut

Taula resum: què canvia segons on la posis

Al `SELECT`: la columna calculada

La mateixa consulta amb `LEFT JOIN` i `GROUP BY`

Al `FROM`: la taula derivada

L'àlies obligatori

Cas 1: agregar dues vegades — el tiquet mitjà, des del seu origen

Cas 2: agregar i després filtrar

Cas 3: dos agregats de granularitat diferent — el problema dels ports

`LATERAL`: la taula derivada que sí que es pot correlacionar

Al `WHERE` i al `HAVING`

A `INSERT`, `UPDATE` i `DELETE`

Taula de decisió: donat un problema, on posar-la

Errors habituals i consells

Exercicis

Exercici 1

Exercici 2

Exercici 3

Solucions

Conclusió

Curs de SQL

Mòdul 1: Introducció a SQL

Mòdul 2: Consultes bàsiques de SQL

Mòdul 3: Treballar amb múltiples taules

Mòdul 4: Filtratge avançat de dades

Mòdul 5: Manipulació de dades

Mòdul 6: Funcions avançades de SQL

Mòdul 7: Subconsultes i consultes imbricades

Mòdul 8: Índexs i optimització del rendiment

Mòdul 9: Transaccions i concurrència

Mòdul 10: Temes avançats

Mòdul 11: SQL a la pràctica

Mòdul 12: Projecte final

Utilitzar subconsultes en les clàusules SELECT, FROM i WHERE

Contingut

Taula resum: què canvia segons on la posis

Al SELECT: la columna calculada

La mateixa consulta amb LEFT JOIN i GROUP BY

Al FROM: la taula derivada

L'àlies obligatori

Cas 1: agregar dues vegades — el tiquet mitjà, des del seu origen

Cas 2: agregar i després filtrar

Cas 3: dos agregats de granularitat diferent — el problema dels ports

LATERAL: la taula derivada que sí que es pot correlacionar

Al WHERE i al HAVING

A INSERT, UPDATE i DELETE

Taula de decisió: donat un problema, on posar-la

Errors habituals i consells

Exercicis

Exercici 1

Exercici 2

Exercici 3

Solucions

Conclusió

Curs de SQL

Mòdul 1: Introducció a SQL

Mòdul 2: Consultes bàsiques de SQL

Mòdul 3: Treballar amb múltiples taules

Mòdul 4: Filtratge avançat de dades

Mòdul 5: Manipulació de dades

Mòdul 6: Funcions avançades de SQL

Mòdul 7: Subconsultes i consultes imbricades

Mòdul 8: Índexs i optimització del rendiment

Mòdul 9: Transaccions i concurrència

Mòdul 10: Temes avançats

Mòdul 11: SQL a la pràctica

Mòdul 12: Projecte final

Al `SELECT`: la columna calculada

La mateixa consulta amb `LEFT JOIN` i `GROUP BY`

Al `FROM`: la taula derivada

`LATERAL`: la taula derivada que sí que es pot correlacionar

Al `WHERE` i al `HAVING`

A `INSERT`, `UPDATE` i `DELETE`