Frågebearbetningsordning

Introduktion till Oracle SQL

Hadrien Lacroix

Content Developer

Varför spelar bearbetningsordningen roll?

Optimera dina frågor
- Inga oönskade resultat
- Snabbare körning

Exempel

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

Exempel

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

Exempel

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

Exempel

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

Exempel

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

Exempel

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

Exempel

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

Vad kan gå fel?

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

Alias kan inte användas i WHERE, GROUP BY och HAVING
Alias kan användas i ORDER BY

Vad kan gå fel?

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

Aggregerade värden kan inte filtreras i WHERE-satsen
Aggregerade värden kan filtreras i HAVING-satsen

Vad kan gå fel?

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

Enskilda rader kan inte filtreras i HAVING-satsen
Enskilda rader kan filtreras i WHERE-satsen

Frågans körordning

FROM och JOIN: avgör vilka data som frågas
WHERE: filtrera enskilda rader
GROUP BY: gruppera rader
HAVING: filtrera grupper
SELECT: välj kolumner och tillämpa funktioner
DISTINCT: ta bort dubbletter
UNION, UNION ALL, INTERSECT, MINUS: tillämpa mängdoperatorer
ORDER BY: sortera rader

Nu kör vi en övning!

Introduktion till Oracle SQL