Ordine di elaborazione delle query

Introduzione a Oracle SQL

Hadrien Lacroix

Content Developer

Perché conta l'ordine di elaborazione?

Ottimizza le tue query
- Niente risultati indesiderati
- Esecuzione più rapida

Esempio

    SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
    WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
    HAVING AVG(Total) > 100
    ORDER BY Average DESC

Esempio

    SELECT BillingCountry, AVG(Total) > 100) AS Average
->  FROM Invoice
    WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
    HAVING AVG(Total) > 100
    ORDER BY Average DESC

Esempio

    SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
->  WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
    HAVING AVG(Total) > 100
    ORDER BY Average DESC

Esempio

    SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
    WHERE BillingCity <> 'Paris' 
->  GROUP BY BillingCountry
    HAVING AVG(Total) > 100
    ORDER BY Average DESC

Esempio

    SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
    WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
->  HAVING AVG(Total) > 100
    ORDER BY Average DESC

Esempio

->  SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
    WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
    HAVING AVG(Total) > 100
    ORDER BY Average DESC

Esempio

    SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
    WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
    HAVING AVG(Total) > 100
->  ORDER BY Average DESC

Cosa può andare storto?

SELECT BillingCountry, 
       AVG(Total) > 100) AS Average
FROM Invoice
WHERE BillingCity <> 'Paris' 
GROUP BY BillingCountry
HAVING AVG(Total) > 100
ORDER BY Average DESC

Gli alias non si possono usare in WHERE, GROUP BY e HAVING
Gli alias si possono usare in ORDER BY

Cosa può andare storto?

SELECT BillingCountry, 
       AVG(Total) > 100) AS Average
FROM Invoice
WHERE BillingCity <> 'Paris' 
GROUP BY BillingCountry
HAVING AVG(Total) > 100
ORDER BY Average DESC

I valori aggregati non si possono filtrare nella clausola WHERE
I valori aggregati si possono filtrare nella clausola HAVING

Cosa può andare storto?

SELECT BillingCountry, 
       AVG(Total) > 100) AS Average
FROM Invoice
WHERE BillingCity <> 'Paris' 
GROUP BY BillingCountry
HAVING AVG(Total) > 100)
ORDER BY Average DESC

Le singole righe non si possono filtrare nella clausola HAVING
Le singole righe si possono filtrare nella clausola WHERE

Ordine di esecuzione della query

FROM e JOIN: determinano quali dati interrogare
WHERE: filtra le singole righe
GROUP BY: raggruppa le righe
HAVING: filtra i gruppi
SELECT: seleziona colonne e applica funzioni sulle colonne
DISTINCT: rimuove i duplicati
UNION, UNION ALL, INTERSECT, MINUS: applica operatori insiemistici
ORDER BY: ordina le righe

Esercitiamoci!

Introduzione a Oracle SQL