Guida per principianti al vero ordine delle operazioni SQL

Il linguaggio SQL è molto intuitivo. Finché non lo è.

Nel corso degli anni, molte persone hanno criticato il linguaggio SQL per diverse ragioni. Per esempio: Gli IDE non possono facilmente indovinare quali opzioni di completamento automatico offrire, perché finché non si specifica la clausola FROM, non ci sono tabelle nello scopo (ancora):

-- Don't you wish this would be completed to first_name?SELECT first_na...-- Aaah, now it works:SELECT first_na...FROM customer

Queste cose sono strane, perché l’ordine lessicale delle operazioni non corrisponde all’ordine logico delle operazioni. Noi umani possiamo a volte (spesso) capire intuitivamente questa differenza d’ordine. Ad esempio, sappiamo che stiamo per selezionare dalla tabella dei clienti. Ma l’IDE non lo sa.

GROUP BY contribuisce alla maggior confusione

Quando uno sviluppatore junior / principiante di SQL inizia a lavorare con SQL, abbastanza rapidamente, scoprirà l’aggregazione e GROUP BY. E scriveranno rapidamente cose come:

SELECT count(*)FROM customer

Ecco, abbiamo 200 clienti!

E poi:

SELECT count(*)FROM customerWHERE first_name = 'Steve'

Wow, 90 di loro si chiamano Steve! Interessante. Scopriamo quanti ne abbiamo per nome…

SELECT first_name, count(*)FROM customerGROUP BY first_name

Ahaa!

FIRST_NAME COUNT------------------Steve 90Jane 80Joe 20Janet 10

Molto bello. Ma sono tutti uguali? Controlliamo anche il cognome

SELECT first_name, last_name, count(*)FROM customerGROUP BY first_name

Oops!

ORA-00979: not a GROUP BY expression

Cavolo, cosa significa? (nota: sfortunatamente, gli utenti MySQL che non usano la modalità STRICT otterranno ancora un risultato con cognomi arbitrari, quindi un nuovo utente MySQL non capirà il loro errore)

Come si può spiegare facilmente questo a un principiante di SQL? Sembra ovvio per i “professionisti”, ma è davvero ovvio? È abbastanza ovvio da poterlo spiegare facilmente ad un principiante? Pensateci. Perché ognuna di queste affermazioni è semanticamente corretta o sbagliata?

Il problema è legato alla sintassi

La sintassi SQL funziona in modo simile alla lingua inglese. È un comando. Cominciamo i comandi con dei verbi. Il verbo è SELECT (o INSERT, UPDATE, DELETE, CREATE, DROP, ecc. ecc.)

Purtroppo, il linguaggio umano è incredibilmente inadatto al mondo molto più formale della programmazione. Mentre offre una certa consolazione ai nuovi utenti (possibilmente non programmatori) che sono principianti assoluti, rende solo le cose difficili per tutti gli altri. Tutte le diverse clausole SQL hanno interdipendenze estremamente complesse. Per esempio:

• In presenza di una clausola GROUP BY, solo espressioni costruite da espressioni GROUP BY (o loro dipendenze funzionali), o funzioni aggregate possono essere usate nelle clausole HAVING, SELECT e ORDER BY.
• Per semplicità, non parliamo nemmeno di GROUPING SETS
• In effetti, ci sono anche alcuni casi in cui GROUP BY è implicito. Ad es. se si scrive una clausola HAVING “nuda”
• Una singola funzione aggregata nella clausola SELECT (in assenza di GROUP BY) forzerà l’aggregazione in una singola riga
• In effetti, questo può anche essere implicito mettendo quella funzione aggregata in ORDER BY (per qualsiasi motivo)
• Si possono ORDER BYqualche espressione che fa riferimento a qualsiasi colonna della clausola FROM senza SELECTfarlo. Ma questo non è più vero se si scrive SELECT DISTINCT
• …

La lista è infinita. Se ti interessa, puoi leggere i documenti standard SQL e verificare quante strane e complicate interdipendenze esistono tra le molte clausole dell’istruzione SELECT.

Può mai essere compreso?

Per fortuna, sì! C’è un semplice trucco, che spiego sempre ai delegati che visitano le mie Masterclass SQL. L’ordine lessicale (sintattico) delle operazioni SQL (clausole) non corrisponde affatto all’ordine logico delle operazioni (anche se, a volte, coincidono). Grazie ai moderni ottimizzatori, l’ordine non corrisponde nemmeno all’ordine reale delle operazioni, quindi abbiamo davvero: ordine sintattico -> logico -> reale, ma lasciamo questo da parte per ora.

L’ordine logico delle operazioni è il seguente (per “semplicità” sto tralasciando le cose specifiche del venditore come CONNECT BY, MODEL, MATCH_RECOGNIZE, PIVOT, UNPIVOT e tutte le altre):

• FROM: Questa è effettivamente la prima cosa che succede, logicamente. Prima di ogni altra cosa, carichiamo tutte le righe da tutte le tabelle e le uniamo. Prima che tu urli e ti arrabbi: Di nuovo, questo è ciò che accade prima logicamente, non realmente. L’ottimizzatore molto probabilmente non farà questa operazione per prima, sarebbe stupido, ma accederà a qualche indice basato sulla clausola WHERE. Ma di nuovo, logicamente, questo accade per primo. Inoltre: tutte le clausole JOIN sono in realtà parte di questa clausola FROM. JOIN è un operatore in algebra relazionale. Proprio come + e - sono operatori in aritmetica. Non è una clausola indipendente, come SELECT o FROM
• WHERE: Una volta che abbiamo caricato tutte le righe dalle tabelle precedenti, possiamo ora buttarle via di nuovo usando WHERE
• GROUP BY: Se volete, potete prendere le righe che rimangono dopo WHERE e metterle in gruppi o secchi, dove ogni gruppo contiene lo stesso valore per l’espressione GROUP BY (e tutte le altre righe sono messe in una lista per quel gruppo). In Java, si otterrebbe qualcosa come: Map<String, List<Row>>. Se specificate una clausola GROUP BY, allora le vostre righe effettive contengono solo le colonne del gruppo, non più le colonne rimanenti, che sono ora in quella lista. Quelle colonne nella lista sono visibili solo alle funzioni di aggregazione che possono operare su quella lista. Vedi sotto.
• aggregazioni: Questo è importante da capire. Non importa dove metti la tua funzione aggregata sintatticamente (cioè nella clausola SELECT o nella clausola ORDER BY), questo è il passo in cui le funzioni aggregate sono calcolate. Subito dopo GROUP BY. (ricordate: logicamente. I database intelligenti possono averle calcolate prima, in realtà). Questo spiega perché non potete mettere una funzione aggregata nella clausola WHERE, perché il suo valore non è ancora accessibile. La clausola WHERE avviene logicamente prima del passo di aggregazione. Le funzioni aggregate possono accedere alle colonne che avete messo in “questa lista” per ogni gruppo, sopra. Dopo l’aggregazione, “questa lista” scomparirà e non sarà più disponibile. Se non avete una clausola GROUP BY, ci sarà solo un grande gruppo senza alcuna chiave, contenente tutte le righe.
• HAVING: … ma ora potete accedere ai valori delle funzioni di aggregazione. Per esempio, potete controllare che count(*) > 1 nella clausola HAVING. Poiché HAVING è dopo GROUP BY (o implica GROUP BY), non possiamo più accedere a colonne o espressioni che non erano colonne GROUP BY.
• WINDOW: Se state usando la fantastica funzione finestra, questo è il passo in cui vengono calcolati tutti. Solo ora. E la cosa bella è che, poiché abbiamo già calcolato (logicamente!) tutte le funzioni aggregate, possiamo annidare le funzioni aggregate nelle funzioni finestra. Quindi va benissimo scrivere cose come sum(count(*)) OVER () o row_number() OVER (ORDER BY count(*)). Il fatto che le funzioni finestra siano calcolate logicamente solo ora spiega anche perché potete metterle solo nelle clausole SELECT o ORDER BY. Non sono disponibili nella clausola WHERE, cosa che succedeva prima. Notate che PostgreSQL e Sybase SQL Anywhere hanno una vera clausola WINDOW!
• SELECT: Finalmente. Ora possiamo usare tutte le righe prodotte dalle clausole di cui sopra e creare nuove righe / tuple da esse usando SELECT. Possiamo accedere a tutte le funzioni finestra che abbiamo calcolato, tutte le funzioni aggregate che abbiamo calcolato, tutte le colonne di raggruppamento che abbiamo specificato, o se non abbiamo raggruppato/aggregato, possiamo usare tutte le colonne dalla nostra clausola FROM. Ricordate: Anche se sembra che stiamo aggregando roba dentro SELECT, questo è successo molto tempo fa, e la dolce dolce funzione count(*) non è altro che un riferimento al risultato.
• DISTINCT: Sì! DISTINCT avviene dopo SELECT, anche se è messo prima della vostra lista di colonne SELECT, sintatticamente parlando. Ma pensateci. Ha perfettamente senso. In quale altro modo possiamo rimuovere righe distinte, se non conosciamo ancora tutte le righe (e le loro colonne)?
• UNION, INTERSECT, EXCEPT: Questo è un gioco da ragazzi. Un UNION è un operatore che collega due subquery. Tutto ciò di cui abbiamo parlato finora era una subquery. L’output di un’unione è una nuova query contenente gli stessi tipi di riga (cioè le stesse colonne) della prima sottoquery. Di solito. Perché in Wacko Oracle, la penultima subquery è quella giusta per definire il nome della colonna. Database Oracle, il troll sintattico 😉
• ORDER BY: Ha totalmente senso rimandare la decisione di ordinare un risultato fino alla fine, perché tutte le altre operazioni potrebbero usare hashmap, internamente, quindi qualsiasi ordine intermedio potrebbe essere nuovamente perso. Quindi ora possiamo ordinare il risultato. Normalmente, potete accedere a molte righe dalla clausola ORDER BY, incluse le righe (o espressioni) che non avete SELECT. Ma quando hai specificato DISTINCT, prima, non puoi più ordinare per righe/espressioni che non sono state selezionate. Perché? Perché l’ordinamento sarebbe abbastanza indefinito.
• OFFSET: Non usare l’offset
• LIMIT, FETCH, TOP: Ora, i database sani mettono la clausola LIMIT (MySQL, PostgreSQL) o FETCH (DB2, Oracle 12c, SQL Server 2012) alla fine, sintatticamente. Ai vecchi tempi, Sybase e SQL Server pensavano che fosse una buona idea avere TOP come parola chiave in SELECT. Come se l’ordine corretto di SELECT DISTINCT non fosse già abbastanza confuso.

Ecco, ci siamo. Ha completamente senso. E se si vuole fare qualcosa che non è nel “giusto ordine”, il trucco più semplice è sempre quello di ricorrere a una tabella derivata. Per esempio, quando volete raggruppare una funzione di finestra:

Perché funziona? Perché:

• Nella tabella derivata, FROM avviene prima, e poi viene calcolato il WINDOW, quindi il secchio viene SELECTed.
• Il SELECTesterno può ora trattare il risultato del calcolo di questa funzione finestra come qualsiasi tabella ordinaria nella clausola FROM, quindi GROUP BY una colonna ordinaria, poi aggregare, poi SELECT

Rivediamo i nostri esempi originali con una spiegazione del perché funzionano o perché non funzionano.

Pensate sempre all’ordine logico delle operazioni

Se non scrivete spesso SQL, la sintassi può davvero confondere. Specialmente GROUP BYe le aggregazioni “infettano” il resto dell’intera clausola SELECT, e le cose diventano davvero strane. Di fronte a questa stranezza, abbiamo due opzioni:

• Incazzarsi e urlare contro i progettisti del linguaggio SQL
• Accettare il nostro destino, chiudere gli occhi, dimenticare la snytax e ricordare l’ordine delle operazioni logiche

In genere consiglio la seconda, perché allora le cose cominciano ad avere molto più senso, compreso il bellissimo calcolo delle entrate cumulative giornaliere qui sotto, che annida le entrate giornaliere (SUM(amount) funzione aggregata) dentro le entrate cumulative (SUM(...) OVER (...) funzione finestra):

SELECT payment_date, SUM(SUM(amount)) OVER (ORDER BY payment_date) AS revenueFROM paymentGROUP BY payment_date

… perché le aggregazioni avvengono logicamente prima delle funzioni finestra.

Caveat: clausola ORDER BY

Ci sono alcuni caveat intorno alla clausola ORDER BY, che potrebbero contribuire ad ulteriore confusione. Per default, continua assumendo che l’ordine logico delle operazioni sia corretto. Ma poi, ci sono alcuni casi speciali, in particolare:

• In assenza di una clausola DISTINCT
• In assenza di operazioni set come UNION

È possibile fare riferimento a espressioni in ORDER BY, che non sono proiettate da SELECT. La seguente query va perfettamente bene nella maggior parte dei database:

SELECT first_name, last_nameFROM actorORDER BY actor_id

C’è una proiezione “virtuale” / implicita di ACTOR_ID, come se avessimo scritto:

SELECT first_name, last_name, actor_idFROM actorORDER BY actor_id

Ma poi, rimosso di nuovo la colonna ACTOR_ID dal risultato. Questo è molto comodo, anche se potrebbe portare a qualche confusione sulla semantica e l’ordine delle operazioni. In particolare, non potete usare ad esempio DISTINCT in una situazione simile. La seguente query non è valida:

SELECT DISTINCT first_name, last_nameFROM actorORDER BY actor_id -- Oops

Perché, cosa succede se ci sono due attori con lo stesso nome ma con ID molto diversi? L’ordinamento sarebbe ora indefinito.

Con le operazioni di set, è ancora più chiaro perché questo non è permesso:

SELECT first_name, last_nameFROM actorUNIONSELECT first_name, last_nameFROM customerORDER BY actor_id -- Oops

In questo caso, la colonna ACTOR_ID non è presente sulla tabella CUSTOMER, quindi la query non ha alcun senso.

Altre letture

Vuoi saperne di più? Abbiamo anche questi articoli da leggere:

• SQL GROUP BY e dipendenze funzionali: Una caratteristica molto utile
• Come avrebbe dovuto essere progettato SQL GROUP BY – Come il GROUP BY implicito di Neo4j
• Come tradurre SQL GROUP BY e aggregazioni in Java 8
• Capisci davvero le clausole GROUP BY e HAVING di SQL?
• GROUP BY ROLLUP / CUBE