Viste e tabelle temporanee in SQL

Nelle lezioni precedenti abbiamo visto come utilizzare query, JOIN, subquery, CTE e altri strumenti per recuperare ed elaborare i dati.

Quando le query diventano particolarmente complesse, però, può essere utile riutilizzare una determinata elaborazione senza dover riscrivere ogni volta l'intera query.

MySQL mette a disposizione, tra gli altri strumenti, le viste e le tabelle temporanee. Anche se entrambe possono semplificare il lavoro con i dati, funzionano in modo molto diverso:

Cosa sono e come creare le viste

Una vista (view) è una sorta di tabella virtuale definita attraverso una query SELECT.

Una vista non crea una copia dei dati presenti nelle tabelle. Memorizza invece una query che viene utilizzata per ottenere e mostrare i dati desiderati ogni volta che la vista viene interrogata.

La sintassi da usare è: CREATE VIEW nome_vista AS query;.

Per esempio, supponiamo di avere una tabella utenti con colonne id, nome, cognome, email, attivo e di voler utilizzare frequentemente soltanto gli utenti attivi.

Potremmo creare una vista:

CREATE VIEW utenti_attivi AS
SELECT id, nome, cognome, email
FROM utenti
WHERE attivo = TRUE;

A questo punto possiamo utilizzare la vista come se fosse una tabella:

SELECT *
FROM utenti_attivi;

Vantaggi delle viste

Uno dei principali vantaggi delle viste è la possibilità di nascondere query complesse dietro un'interfaccia più semplice. 

Supponiamo di avere le tabelle utenti, ordini e prodotti e di voler ottenere frequentemente il nome del cliente e il totale dei suoi ordini. Potremmo creare una vista:

CREATE VIEW riepilogo_ordini AS
SELECT
    u.id,
    u.nome,
    u.cognome,
    COUNT(o.id) AS numero_ordini,
    SUM(o.totale) AS totale_speso
FROM utenti AS u
JOIN ordini AS o
    ON u.id = o.utente_id
GROUP BY
    u.id,
    u.nome,
    u.cognome;

A questo punto, invece di ripetere l'intera query che collega le tabelle e raggruppa i dati con GROUP BY ogni volta, possiamo scrivere:

SELECT *
FROM riepilogo_ordini;

Modificare una vista

Se vogliamo modificare la definizione di una vista, possiamo utilizzare CREATE OR REPLACE VIEW. Ad esempio, possiamo sostituire la definizione precedente della vista con quella nuova:

CREATE OR REPLACE VIEW utenti_attivi AS
SELECT id, nome, cognome
FROM utenti
WHERE attivo = TRUE;

Possiamo anche eliminare una vista con:

DROP VIEW utenti_attivi;

Cosa sono e come creare le tabelle temporanee

Le tabelle temporanee sono vere e proprie tabelle create per essere utilizzate temporaneamente durante una sessione.

La sintassi è: CREATE TEMPORARY TABLE nome_tabella ( ... );.

Ad esempio, possiamo creare utenti_selezionati:

CREATE TEMPORARY TABLE utenti_selezionati (
    id INT,
    nome VARCHAR(100)
);

Possiamo poi utilizzarla normalmente, per l'inserimento e la selezione di dati:

INSERT INTO utenti_selezionati
VALUES
    (1, 'Marco'),
    (2, 'Anna');

SELECT * FROM utenti_selezionati;

Una tabella creata con CREATE TEMPORARY TABLE rimane disponibile durante la sessione corrente del client MySQL. Quando la connessione viene chiusa, MySQL elimina automaticamente le tabelle temporanee create durante quella sessione.

Non è quindi necessario eliminarle manualmente alla fine della sessione, ma possiamo comunque farlo prima utilizzando:

DROP TEMPORARY TABLE utenti_selezionati;

Creare una tabella temporanea da una query

Una delle possibilità più utili è creare una tabella temporanea partendo da una query di SELECT. Ad esempio:

CREATE TEMPORARY TABLE prodotti_costosi AS
SELECT id, nome, prezzo
FROM prodotti
WHERE prezzo > 500;

MySQL crea una nuova tabella temporanea contenente il risultato della query. Possiamo quindi utilizzarla successivamente, con query e condizioni che preferiamo.