Subconsultes

Què és una subconsulta?

Una subconsulta (subquery o consulta imbricada) és una consulta SQL completa inserida dins d'una altra consulta. La consulta interior s'executa primer i el seu resultat s'usa com a entrada per a la consulta exterior.

Les subconsultes poden aparèixer en:

• La clàusula SELECT (subconsultes escalars)
• La clàusula FROM (derived tables)
• La clàusula WHERE
• La clàusula HAVING

---

Quan usar cada tipus de subconsulta

flowchart TD
    Q["Necessito comparar\nfiles d'una taula\namb dades d'una altra"]
    Q --> A{"Quants valors\nretorna la\nsubconsulta?"}

    A -->|"1 valor\n(escalar)"| B["Subconsulta escalar\nWHERE col = (SELECT ...)"]
    A -->|"N valors\n(columna)"| C{"Tipus de\ncomparació"}
    A -->|"N files i columnes"| D["Derived table\nFROM (SELECT ...) AS t"]

    C -->|"Existència\n(TRUE/FALSE)"| E["EXISTS / NOT EXISTS\n(s'atura en trobar la 1a fila)"]
    C -->|"Pertinença\na una llista"| F{"Pot haver\nNULL a la llista?"}
    C -->|"Comparació\namb tots o algun"| G["ALL / ANY\nWHERE col > ALL(...)"]

    F -->|"No"| H["IN / NOT IN\nWHERE col IN (SELECT ...)"]
    F -->|"Sí"| I["EXISTS / NOT EXISTS\n(NOT IN amb NULLs dona resultats inesperats)"]

    B --> TIP1["Millora: si és freqüent,\nconsidereu un JOIN"]
    E --> TIP2["Recomanat per a\nsubconsultes correlacionades"]

    style E fill:#1b5e20,stroke:#2e7d32,color:#fff
    style I fill:#b71c1c,stroke:#c62828,color:#fff
    style TIP1 fill:#e8f5e9,stroke:#388e3c,color:#1b5e20
    style TIP2 fill:#e8f5e9,stroke:#388e3c,color:#1b5e20

Resum ràpid:

Situació	Construcció recomanada
Valor únic a comparar	WHERE col = (SELECT ...)
Existència de files relacionades	WHERE EXISTS (SELECT 1 ...)
Pertinença a un conjunt (sense NULLs)	WHERE col IN (SELECT ...)
Pertinença a un conjunt (pot haver NULLs)	WHERE EXISTS (SELECT 1 ...)
Comparació amb tots els valors	WHERE col > ALL (SELECT ...)
Comparació amb almenys un valor	WHERE col > ANY (SELECT ...)
Subconsulta reusable o complexa	WITH nom AS (SELECT ...) SELECT ...

---

Subconsultes escalars

Una subconsulta escalar retorna exactament una fila i una columna (un únic valor). Es pot usar allà on s'esperaria un valor únic.

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Productes amb preu superior a la mitja
SELECT nom, preu
FROM productes
WHERE preu > (SELECT AVG(preu) FROM productes)
ORDER BY preu DESC;

-- Última comanda de cada client (subconsulta escalar correlacionada)
SELECT
    c.nom || ' ' || c.cognoms AS client,
    (SELECT MAX(data_com) FROM comandes co WHERE co.id_client = c.id_client) AS ultima_comanda
FROM clients c;

-- Productes amb preu superior a la mitja
SELECT nom, preu
FROM productes
WHERE preu > (SELECT AVG(preu) FROM productes)
ORDER BY preu DESC;

-- Productes amb preu superior a la mitja
SELECT nom, preu
FROM productes
WHERE preu > (SELECT AVG(preu) FROM productes)
ORDER BY preu DESC;

-- Productes amb preu superior a la mitja
SELECT nom, preu
FROM productes
WHERE preu > (SELECT AVG(preu) FROM productes)
ORDER BY preu DESC;

-- Última comanda de cada client (subconsulta escalar correlacionada)
SELECT
    c.nom || ' ' || c.cognoms AS client,
    (SELECT MAX(data_com) FROM comandes co WHERE co.id_client = c.id_client) AS ultima_comanda
FROM clients c;

---

Subconsultes en el FROM (Derived Tables)

Quan una subconsulta apareix en la clàusula FROM, s'anomena derived table o taula derivada. Ha de tenir obligatòriament un àlies.

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- El top 3 de clients per volum de compra (usant derived table)
SELECT client, total_gastat, RANK() OVER (ORDER BY total_gastat DESC) AS posicio
FROM (
    SELECT
        c.nom || ' ' || c.cognoms AS client,
        SUM(co.total) AS total_gastat
    FROM clients c
    JOIN comandes co ON c.id_client = co.id_client
    WHERE co.estat = 'entregada'
    GROUP BY c.id_client, c.nom, c.cognoms
) AS resum_vendes
ORDER BY total_gastat DESC
LIMIT 3;

SELECT client, total_gastat
FROM (
    SELECT
        CONCAT(c.nom, ' ', c.cognoms) AS client,
        SUM(co.total) AS total_gastat
    FROM clients c
    JOIN comandes co ON c.id_client = co.id_client
    WHERE co.estat = 'entregada'
    GROUP BY c.id_client, c.nom, c.cognoms
) AS resum_vendes
ORDER BY total_gastat DESC
LIMIT 3;

SELECT TOP 3 client, total_gastat
FROM (
    SELECT
        c.nom + ' ' + c.cognoms AS client,
        SUM(co.total) AS total_gastat
    FROM clients c
    JOIN comandes co ON c.id_client = co.id_client
    WHERE co.estat = 'entregada'
    GROUP BY c.id_client, c.nom, c.cognoms
) AS resum_vendes
ORDER BY total_gastat DESC;

-- Oracle 12c+: FETCH FIRST
SELECT client, total_gastat
FROM (
    SELECT
        c.nom || ' ' || c.cognoms AS client,
        SUM(co.total) AS total_gastat
    FROM clients c
    JOIN comandes co ON c.id_client = co.id_client
    WHERE co.estat = 'entregada'
    GROUP BY c.id_client, c.nom, c.cognoms
) resum_vendes   -- Oracle no requereix AS per als àlies de taula derivada
ORDER BY total_gastat DESC
FETCH FIRST 3 ROWS ONLY;

---

Subconsultes correlacionades

Una subconsulta correlacionada fa referència a columnes de la consulta exterior. A diferència de les subconsultes independents (que s'executen una sola vegada), les correlacionades s'executen una vegada per cada fila de la consulta exterior.

S'identifiquen perquè a la clàusula WHERE de la subconsulta apareix una columna de la taula exterior.

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Clients que han gastat més que la mitja global
SELECT c.nom, c.cognoms,
       (SELECT SUM(total) FROM comandes co WHERE co.id_client = c.id_client) AS total_gastat
FROM clients c
WHERE (SELECT SUM(total) FROM comandes co WHERE co.id_client = c.id_client)
      > (SELECT AVG(total) FROM comandes);

-- Productes que s'han venut per sobre de la mitja de la seva categoria
SELECT p.nom, p.preu,
       (SELECT AVG(preu) FROM productes p2 WHERE p2.id_categoria = p.id_categoria) AS mitja_cat
FROM productes p
WHERE p.preu > (
    SELECT AVG(preu)
    FROM productes p2
    WHERE p2.id_categoria = p.id_categoria
);

-- Productes que s'han venut per sobre de la mitja de la seva categoria
SELECT p.nom, p.preu
FROM productes p
WHERE p.preu > (
    SELECT AVG(preu)
    FROM productes p2
    WHERE p2.id_categoria = p.id_categoria
);

-- Productes que s'han venut per sobre de la mitja de la seva categoria
SELECT p.nom, p.preu
FROM productes p
WHERE p.preu > (
    SELECT AVG(preu)
    FROM productes p2
    WHERE p2.id_categoria = p.id_categoria
);

-- Productes que s'han venut per sobre de la mitja de la seva categoria
SELECT p.nom, p.preu,
       (SELECT AVG(preu) FROM productes p2 WHERE p2.id_categoria = p.id_categoria) AS mitja_cat
FROM productes p
WHERE p.preu > (
    SELECT AVG(preu)
    FROM productes p2
    WHERE p2.id_categoria = p.id_categoria
);

Rendiment de les subconsultes correlacionades

Com que la subconsulta s'executa per cada fila, el cost pot ser molt elevat en taules grans. En molts casos és possible reescriure-la com un JOIN amb agrupació, que el SGBD optimitza molt millor:

-- Versió amb subconsulta correlacionada (lenta en taules grans)
SELECT p.nom, p.preu
FROM productes p
WHERE p.preu > (SELECT AVG(preu) FROM productes p2 WHERE p2.id_categoria = p.id_categoria);

-- Versió equivalent amb JOIN (generalment més ràpida)
SELECT p.nom, p.preu
FROM productes p
JOIN (SELECT id_categoria, AVG(preu) AS mitja FROM productes GROUP BY id_categoria) m
  ON p.id_categoria = m.id_categoria
WHERE p.preu > m.mitja;

---

Subconsultes amb IN i NOT IN

La subconsulta retorna un conjunt de valors que s'usa com a llista per a IN o NOT IN:

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Clients que han fet almenys una comanda a Barcelona
SELECT nom, cognoms
FROM clients
WHERE id_client IN (
    SELECT DISTINCT id_client
    FROM comandes
    WHERE id_adreça_enviament IN (
        SELECT id_adreça FROM adreces WHERE poblacio = 'Barcelona'
    )
);

-- Productes que NO s'han venut mai
SELECT nom, preu
FROM productes
WHERE id_producte NOT IN (
    SELECT DISTINCT id_producte FROM linies_comanda
);

-- Productes que NO s'han venut mai
SELECT nom, preu
FROM productes
WHERE id_producte NOT IN (
    SELECT DISTINCT id_producte FROM linies_comanda
);

-- Productes que NO s'han venut mai
SELECT nom, preu
FROM productes
WHERE id_producte NOT IN (
    SELECT DISTINCT id_producte FROM linies_comanda
);

-- Productes que NO s'han venut mai
SELECT nom, preu
FROM productes
WHERE id_producte NOT IN (
    SELECT DISTINCT id_producte FROM linies_comanda
);

NOT IN i els NULL

Si la subconsulta de NOT IN retorna algun valor NULL, tota la condició NOT IN retorna NULL (no filtra res). Quan la columna pot ser NULL, és més segur usar NOT EXISTS:

-- Més segur que NOT IN si pot haver NULLs
SELECT nom, preu
FROM productes p
WHERE NOT EXISTS (
    SELECT 1 FROM linies_comanda lc WHERE lc.id_producte = p.id_producte
);

---

Subconsultes amb ANY i ALL

ANY (o SOME) i ALL permeten comparar un valor amb tots els valors retornats per una subconsulta, combinant-los amb qualsevol operador de comparació (>, <, =, <>, >=, <=).

Operador	Equivalent	Significat
= ANY	IN	Igual a almenys un valor de la llista
<> ALL	NOT IN	Diferent de tots els valors
> ANY	> MIN(...)	Major que almenys un valor
> ALL	> MAX(...)	Major que tots els valors
< ANY	< MAX(...)	Menor que almenys un valor
< ALL	< MIN(...)	Menor que tots els valors

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Productes més cars que ALGUN producte de la categoria 'accessori'
SELECT nom, preu
FROM productes
WHERE preu > ANY (
    SELECT preu FROM productes
    WHERE id_categoria = (SELECT id_categoria FROM categories WHERE nom = 'accessori')
);

-- Productes més cars que TOTS els productes de la categoria 'accessori'
-- (és a dir, més cars que el producte més car de la categoria)
SELECT nom, preu
FROM productes
WHERE preu > ALL (
    SELECT preu FROM productes
    WHERE id_categoria = (SELECT id_categoria FROM categories WHERE nom = 'accessori')
);

-- Clients que han gastat MÉS que qualsevol client de la província de Girona
SELECT c.nom, c.cognoms, SUM(co.total) AS total
FROM clients c
JOIN comandes co ON c.id_client = co.id_client
GROUP BY c.id_client, c.nom, c.cognoms
HAVING SUM(co.total) > ALL (
    SELECT SUM(co2.total)
    FROM clients c2
    JOIN comandes co2 ON c2.id_client = co2.id_client
    WHERE c2.provincia = 'Girona'
    GROUP BY c2.id_client
);

-- Productes més cars que ALGUN producte de la categoria 'accessori'
SELECT nom, preu
FROM productes
WHERE preu > ANY (
    SELECT preu FROM productes
    WHERE id_categoria = (SELECT id_categoria FROM categories WHERE nom = 'accessori')
);

-- Productes més cars que TOTS els productes de la categoria 'accessori'
SELECT nom, preu
FROM productes
WHERE preu > ALL (
    SELECT preu FROM productes
    WHERE id_categoria = (SELECT id_categoria FROM categories WHERE nom = 'accessori')
);

-- Productes més cars que ALGUN producte de la categoria 'accessori'
SELECT nom, preu
FROM productes
WHERE preu > ANY (
    SELECT preu FROM productes
    WHERE id_categoria = (SELECT id_categoria FROM categories WHERE nom = 'accessori')
);

-- Productes més cars que TOTS els productes de la categoria 'accessori'
SELECT nom, preu
FROM productes
WHERE preu > ALL (
    SELECT preu FROM productes
    WHERE id_categoria = (SELECT id_categoria FROM categories WHERE nom = 'accessori')
);

-- Productes més cars que ALGUN producte de la categoria 'accessori'
SELECT nom, preu
FROM productes
WHERE preu > ANY (
    SELECT preu FROM productes
    WHERE id_categoria = (SELECT id_categoria FROM categories WHERE nom = 'accessori')
);

-- Productes més cars que TOTS els productes de la categoria 'accessori'
SELECT nom, preu
FROM productes
WHERE preu > ALL (
    SELECT preu FROM productes
    WHERE id_categoria = (SELECT id_categoria FROM categories WHERE nom = 'accessori')
);

ALL i els valors NULL

Si la subconsulta de ALL retorna algun NULL, la condició pot retornar NULL (resultat indeterminat) en lloc de FALSE o TRUE. Per evitar-ho, filtreu els NULLs a la subconsulta:

WHERE preu > ALL (SELECT preu FROM productes WHERE nom IS NOT NULL)

---

EXISTS i NOT EXISTS

EXISTS comprova si la subconsulta retorna almenys una fila. La subconsulta només ha de tenir sentit (retornar o no files), no importa el valor retornat (per convenció es sol escriure SELECT 1).

EXISTS és generalment més eficient que IN per a subconsultes que retornen molts valors, perquè el SGBD pot aturar-se en trobar la primera coincidència.

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Clients que han fet almenys una comanda
SELECT c.nom, c.cognoms
FROM clients c
WHERE EXISTS (
    SELECT 1 FROM comandes co WHERE co.id_client = c.id_client
);

-- Clients que NO han fet cap comanda en els últims 6 mesos
SELECT c.nom, c.cognoms, c.email
FROM clients c
WHERE NOT EXISTS (
    SELECT 1
    FROM comandes co
    WHERE co.id_client = c.id_client
      AND co.data_com >= CURRENT_DATE - INTERVAL '6 months'
);

-- Productes que estan a totes les comandes d'un client concret
-- (subconsulta doblement correlacionada)
SELECT p.nom
FROM productes p
WHERE NOT EXISTS (
    SELECT 1
    FROM comandes co
    WHERE co.id_client = 1
      AND NOT EXISTS (
          SELECT 1
          FROM linies_comanda lc
          WHERE lc.id_comanda = co.id_comanda
            AND lc.id_producte = p.id_producte
      )
);

-- Clients que han fet almenys una comanda
SELECT c.nom, c.cognoms
FROM clients c
WHERE EXISTS (
    SELECT 1 FROM comandes co WHERE co.id_client = c.id_client
);

-- Clients que NO han fet cap comanda en els últims 6 mesos
SELECT c.nom, c.cognoms, c.email
FROM clients c
WHERE NOT EXISTS (
    SELECT 1
    FROM comandes co
    WHERE co.id_client = c.id_client
      AND co.data_com >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
);

-- Clients que han fet almenys una comanda
SELECT c.nom, c.cognoms
FROM clients c
WHERE EXISTS (
    SELECT 1 FROM comandes co WHERE co.id_client = c.id_client
);

-- Clients que NO han fet cap comanda en els últims 6 mesos
SELECT c.nom, c.cognoms, c.email
FROM clients c
WHERE NOT EXISTS (
    SELECT 1
    FROM comandes co
    WHERE co.id_client = c.id_client
      AND co.data_com >= DATEADD(MONTH, -6, GETDATE())
);

-- Clients que han fet almenys una comanda
SELECT c.nom, c.cognoms
FROM clients c
WHERE EXISTS (
    SELECT 1 FROM comandes co WHERE co.id_client = c.id_client
);

-- Clients que NO han fet cap comanda en els últims 6 mesos
SELECT c.nom, c.cognoms, c.email
FROM clients c
WHERE NOT EXISTS (
    SELECT 1
    FROM comandes co
    WHERE co.id_client = c.id_client
      AND co.data_com >= ADD_MONTHS(SYSDATE, -6)
);

---

CTEs (Common Table Expressions) amb WITH

Les CTE (Expressions de Taula Comú) permeten definir subconsultes amb nom que és poden referenciar múltiples vegades. Milloren la llegibilitat de les consultes complexos.

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Equivalent a la derived table anterior, però més llegible
WITH resum_vendes AS (
    SELECT
        c.id_client,
        c.nom || ' ' || c.cognoms AS client,
        SUM(co.total) AS total_gastat,
        COUNT(co.id_comanda) AS num_comandes
    FROM clients c
    JOIN comandes co ON c.id_client = co.id_client
    WHERE co.estat = 'entregada'
    GROUP BY c.id_client, c.nom, c.cognoms
),
stats_globals AS (
    SELECT AVG(total_gastat) AS mitja_global FROM resum_vendes
)
SELECT
    rv.client,
    rv.total_gastat,
    rv.num_comandes,
    CASE
        WHEN rv.total_gastat > sg.mitja_global * 2 THEN 'Premium'
        WHEN rv.total_gastat > sg.mitja_global THEN 'Regular'
        ELSE 'Ocasional'
    END AS segment
FROM resum_vendes rv, stats_globals sg
ORDER BY rv.total_gastat DESC;

-- CTEs suportades des de MySQL 8.0
WITH resum_vendes AS (
    SELECT
        c.id_client,
        CONCAT(c.nom, ' ', c.cognoms) AS client,
        SUM(co.total) AS total_gastat
    FROM clients c
    JOIN comandes co ON c.id_client = co.id_client
    WHERE co.estat = 'entregada'
    GROUP BY c.id_client, c.nom, c.cognoms
)
SELECT * FROM resum_vendes WHERE total_gastat > 500 ORDER BY total_gastat DESC;

WITH resum_vendes AS (
    SELECT
        c.id_client,
        c.nom + ' ' + c.cognoms AS client,
        SUM(co.total) AS total_gastat
    FROM clients c
    JOIN comandes co ON c.id_client = co.id_client
    WHERE co.estat = 'entregada'
    GROUP BY c.id_client, c.nom, c.cognoms
)
SELECT * FROM resum_vendes WHERE total_gastat > 500 ORDER BY total_gastat DESC;

-- CTEs suportades des d'Oracle 9i R2
WITH resum_vendes AS (
    SELECT
        c.id_client,
        c.nom || ' ' || c.cognoms AS client,
        SUM(co.total) AS total_gastat,
        COUNT(co.id_comanda) AS num_comandes
    FROM clients c
    JOIN comandes co ON c.id_client = co.id_client
    WHERE co.estat = 'entregada'
    GROUP BY c.id_client, c.nom, c.cognoms
),
stats_globals AS (
    SELECT AVG(total_gastat) AS mitja_global FROM resum_vendes
)
SELECT
    rv.client,
    rv.total_gastat,
    rv.num_comandes,
    CASE
        WHEN rv.total_gastat > sg.mitja_global * 2 THEN 'Premium'
        WHEN rv.total_gastat > sg.mitja_global THEN 'Regular'
        ELSE 'Ocasional'
    END AS segment
FROM resum_vendes rv, stats_globals sg
ORDER BY rv.total_gastat DESC;

---

CTEs recursives

Una CTE recursiva és una CTE que es referencia a si mateixa. S'utilitza per recórrer dades jeràrquiques (categories amb subcategories, estructures organitzatives, arbres de dependències) sense necessitat de programació procedural.

L'estructura té sempre dues parts unides per UNION ALL:

1. Cas base: la consulta no recursiva que retorna les files inicials (normalment les arrels de la jerarquia).
2. Cas recursiu: la consulta que referencia la pròpia CTE i va expandint el resultat un nivell per iteració.

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Jerarquia de categories (id_categoria_pare és NULL a les arrels)
WITH RECURSIVE jerarquia AS (
    -- Cas base: categories arrel
    SELECT id_categoria, nom, id_categoria_pare, 0 AS nivell,
           nom::TEXT AS ruta
    FROM categories
    WHERE id_categoria_pare IS NULL

    UNION ALL

    -- Cas recursiu: subcategories
    SELECT c.id_categoria, c.nom, c.id_categoria_pare,
           j.nivell + 1,
           j.ruta || ' > ' || c.nom
    FROM categories c
    INNER JOIN jerarquia j ON c.id_categoria_pare = j.id_categoria
)
SELECT REPEAT('  ', nivell) || nom AS estructura, ruta
FROM jerarquia
ORDER BY ruta;

-- CTEs recursives suportades des de MySQL 8.0 / MariaDB 10.2
WITH RECURSIVE jerarquia AS (
    SELECT id_categoria, nom, id_categoria_pare, 0 AS nivell
    FROM categories
    WHERE id_categoria_pare IS NULL

    UNION ALL

    SELECT c.id_categoria, c.nom, c.id_categoria_pare, j.nivell + 1
    FROM categories c
    INNER JOIN jerarquia j ON c.id_categoria_pare = j.id_categoria
)
SELECT REPEAT('  ', nivell), nom, nivell
FROM jerarquia
ORDER BY nivell, nom;

-- SQL Server suporta CTEs recursives sense la paraula RECURSIVE
WITH jerarquia AS (
    SELECT id_categoria, nom, id_categoria_pare, 0 AS nivell
    FROM categories
    WHERE id_categoria_pare IS NULL

    UNION ALL

    SELECT c.id_categoria, c.nom, c.id_categoria_pare, j.nivell + 1
    FROM categories c
    INNER JOIN jerarquia j ON c.id_categoria_pare = j.id_categoria
)
SELECT REPLICATE('  ', nivell) + nom AS estructura, nivell
FROM jerarquia
ORDER BY nivell, nom;

-- Oracle 11g R2+: CTEs recursives estàndard (sense paraula RECURSIVE)
WITH jerarquia(id_categoria, nom, id_categoria_pare, nivell) AS (
    SELECT id_categoria, nom, id_categoria_pare, 0
    FROM categories
    WHERE id_categoria_pare IS NULL

    UNION ALL

    SELECT c.id_categoria, c.nom, c.id_categoria_pare, j.nivell + 1
    FROM categories c
    JOIN jerarquia j ON c.id_categoria_pare = j.id_categoria
)
SELECT LPAD(' ', nivell * 2) || nom AS estructura, nivell
FROM jerarquia
ORDER BY nivell, nom;

-- Oracle: alternativa tradicional amb CONNECT BY (des d'Oracle 8)
SELECT LPAD(' ', (LEVEL - 1) * 2) || nom AS estructura,
       LEVEL - 1 AS nivell
FROM categories
START WITH id_categoria_pare IS NULL
CONNECT BY PRIOR id_categoria = id_categoria_pare
ORDER SIBLINGS BY nom;

Quan usar CTEs recursives

Les CTEs recursives són ideals per a:

• Jerarquies de categories amb profunditat variable (categories > subcategories > subsubcategories)
• Organigrames (empleat → responsable directe → director)
• Llistes de materials (producte → components → subcomponents)
• Grafs de dependències (si el graf no té cicles)

Per evitar bucles infinits, els SGBD limiten el nombre d'iteracions (per defecte 100 a SQL Server, 100 a Oracle). PostgreSQL i MySQL no limiten per defecte però ho podeu controlar:

-- PostgreSQL: limitar profunditat
WITH RECURSIVE ... WHERE nivell < 10 ...

-- SQL Server: limitar profunditat
OPTION (MAXRECURSION 50)

---

CREATE TABLE AS SELECT

CREATE TABLE ... AS SELECT permet crear una nova taula directament a partir del resultat d'una consulta. La taula nova adopta l'estructura i les dades de la consulta, però no hereta índexos, claus primàries ni restriccions.

És molt útil per crear taules de resum, instantànies de dades o taules de treball temporals.

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Crear una taula de resum de vendes de 2024
CREATE TABLE resum_vendes_2024 AS
SELECT
    c.id_client,
    c.nom || ' ' || c.cognoms AS client,
    COUNT(co.id_comanda)       AS num_comandes,
    SUM(co.total)              AS total_gastat
FROM clients c
JOIN comandes co ON c.id_client = co.id_client
WHERE EXTRACT(YEAR FROM co.data_com) = 2024
GROUP BY c.id_client, c.nom, c.cognoms;

-- Afegir la clau primària manualment si cal
ALTER TABLE resum_vendes_2024 ADD PRIMARY KEY (id_client);

-- Crear taula buida amb la mateixa estructura (sense dades)
CREATE TABLE resum_vendes_buit AS
SELECT * FROM resum_vendes_2024 WHERE FALSE;

-- Crear taula de resum
CREATE TABLE resum_vendes_2024 AS
SELECT
    c.id_client,
    CONCAT(c.nom, ' ', c.cognoms) AS client,
    COUNT(co.id_comanda)           AS num_comandes,
    SUM(co.total)                  AS total_gastat
FROM clients c
JOIN comandes co ON c.id_client = co.id_client
WHERE YEAR(co.data_com) = 2024
GROUP BY c.id_client, c.nom, c.cognoms;

-- Afegir clau primària manualment
ALTER TABLE resum_vendes_2024 ADD PRIMARY KEY (id_client);

-- SQL Server usa SELECT ... INTO (no CREATE TABLE AS SELECT)
SELECT
    c.id_client,
    c.nom + ' ' + c.cognoms    AS client,
    COUNT(co.id_comanda)        AS num_comandes,
    SUM(co.total)               AS total_gastat
INTO resum_vendes_2024
FROM clients c
JOIN comandes co ON c.id_client = co.id_client
WHERE YEAR(co.data_com) = 2024
GROUP BY c.id_client, c.nom, c.cognoms;

-- Crear taula buida (SELECT ... INTO amb WHERE 1=0)
SELECT * INTO resum_vendes_buit FROM resum_vendes_2024 WHERE 1 = 0;

-- Crear taula de resum
CREATE TABLE resum_vendes_2024 AS
SELECT
    c.id_client,
    c.nom || ' ' || c.cognoms  AS client,
    COUNT(co.id_comanda)        AS num_comandes,
    SUM(co.total)               AS total_gastat
FROM clients c
JOIN comandes co ON c.id_client = co.id_client
WHERE EXTRACT(YEAR FROM co.data_com) = 2024
GROUP BY c.id_client, c.nom, c.cognoms;

-- Crear taula buida amb la mateixa estructura
CREATE TABLE resum_vendes_buit AS
SELECT * FROM resum_vendes_2024 WHERE 1 = 2;

AC0372/04/05 — Miniactivitat

RA4 · CA4.4, CA4.5, CA4.6

Escriviu subconsultes i CTEs per a:

1. Instal·lacions amb un preu per hora superior a la mitja de totes les instal·lacions.
2. Usuaris que mai han fet una reserva (NOT IN o NOT EXISTS).
3. El dia de la setmana amb mes reserves (GROUP BY + subconsulta o CTE).
4. Reescriviu la consulta 3 usant una CTE per millorar la llegibilitat.
5. Usuaris que han fet reserves a totes les instal·lacions disponibles (EXISTS avançat).